Multiple Databases in CakePHP
There comes a time in life when you need to use multiple databases for a website you have to build 🙂 .
I came across this “problem” when I was working on a portal website and I had to make sure that users can login with the same details on any website that company owned.
So I had to create a common database in which to store the users. Since all the websites were built in CakePHP I had to find a way to make it connect to other databases besides the default one.
If you are familiar with CakePHP you know that the database details are kept in app/config/database.php.
The default database array is:
var $default = array(
'driver' => 'mysql',
'persistent' => false,
'host' => 'localhost',
'login' => 'mysqlusername',
'password' => 'mysqlpassword',
'database' => 'mysqldatabase',
'prefix' => ''
);
Since you’re here now add another database array:
var $database2 = array(
'driver' => 'mysql',
'persistent' => false,
'host' => 'localhost',
'login' => 'mysqlusername2',
'password' => 'mysqlpassword2',
'database' => 'mysqldatabase2',
'prefix' => ''
);
Now open up a model you are using. Let’s say we are using the model User ( located in app/models/user.php ). Open the file and add this function to it:
function changeDataSource($newSource) {
parent::setDataSource($newSource);
parent::__construct();
}
Now in the Users controller ( located in app/controllers/users_controller.php ) add this function:
function changeDbSource($database = 'default') {
$db = ConnectionManager::getInstance();
$connected = $db->getDataSource($database);
if($connected->isConnected()) {
return true;
} else {
return false;
}
}
Here’s an example on how to switch between the databases:
function loginUser() {
// The search is made in the default database
$this->User->find('all', array('conditions' => array('User.username' => $username)));
// Let's do something in the second database database2
$this->User->changeDataSource('database2');
if($this->changeDbSource('database2')) {
// Do something in database 2
}
}
It’s really easy once you get the hang of it.
Bär vacker aftonklänningar att bedövning i partiet.
Categories: CakePHP, Programming, Tutorials
9 Responses to “Multiple Databases in CakePHP”
Very useful and easy !
Thanks for sharing 🙂
So clear, I will use it in my current project. Thanks!
I have been doing some development in cakephp where i have to interact with three databases in one controller. I put your approach in place and completed my module. Everything worked fine on my windows machine, but when i deploy the module on my linux server, my code is not able to connect to different databases of MySQL. I have cross-checked the username’s and access issues, but still the problem persists. Any help would be highly appreciated…
Sabah, I always work on a linux machine ( I use Ubuntu ) and I managed to connect just fine to multiple databases from a single CakePHP project. I believe that I was using 6 databases for a project at work. No issues appeared neither on my machine or the linux server the websites was hosted on.
I think it’s a problem with your machine. Double check usernames and password and if they have access to the databases you want to use.
This is realy simple and Useful information sharing i realy like it ….
dear Sabah Tahir i also use this with 6 different database to connect. I think you may be getting Error because of don’t connect the default database again. Try to Reconnect the database in start when the code reach may be the database connection for the selected database not override.
hope so it help
Very good article.
y there,
Thanks for the post. This was the perfect solution for me when saving the same dataset to multiple databases with the same tables structures. I am using Cakephp 2.3 right now and had to modify your controller code slightly to make it work.
public $dbSet = array(‘wp2’, ‘default’);
private function _changeDbSource($database) {
if(in_array($database, $this->dbSet))
{
$db = new ConnectionManager;
$connected = $db->getDataSource($database);
if($connected->isConnected()) {
return true;
} else {
return false;
}
}
else {
return false;
}
}
Notice that there is no call to ConnectionManager::getInstance() as this was reworked into the ConnectionManager::getDataSource functionality.
HTH Someone,
Ron
Thanks for the update. Glad it worked for you