Sava's Place

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)));

$this->User->changeDataSource('database2');
if($this->changeDbSource('database2')) {
// Do something in database 2
}
}


It’s really easy once you get the hang of it.