Insanityville

How to switch between databases in CakePHP on the fly

By in-sanity on April 14th, 2010

Ever needed to connect to another database in your cake app but got stuck and came with another solution? There is a rather simple way to do this and I think it can be extended to other data sources too (not tested but let me know if you do ).

Let’s get to it.

First you need to add the details of all the databases you want to connect to from within CakePHP. To do this you must go to the database.php file and add the login details for each database. You may end up with something similar to the following code:

class DATABASE_CONFIG {
 
	var $default = array(
		'driver' => 'mysql',
		'persistent' => false,
		'host' => 'localhost',
		'login' => 'root',
		'password' => '',
		'database' => 'db1',
		'prefix' => ''
	);
 
	var $website2 = array(
		'driver' => 'mysql',
		'persistent' => false,
		'host' => 'localhost',
		'login' => 'root',
		'password' => '',
		'database' => 'db2',
		'prefix' => ''
	);
}

Now that we have our database information defined, it’s time toad some code to our AppModel class. Just put the following code in your app_model.php file:

function changeDataSource($newSource)  {
        parent::setDataSource($newSource);
        parent::__construct();
}

In the app_controller.php we must have the following code also:

function changeDbSource($database = 'default') {
        $db = ConnectionManager::getInstance();
        $connected = $db->getDataSource($database);
        if($connected->isConnected()) 
                return true;
        return false;
}

Now we are ready to juggle with our databases as we see fit. Let’s say we want to register a user in multiple applications but we only want one form. We might do something like this in the users_controller.php in the add method:

$user = $this->data;
// set data source for the website1
$this->User->changeDataSource('website2');
if($this->changeDbSource('website2')) {
	$this->User->create($user);
	$this->User->save($user);
}
// change data source to default
$this->User->changeDataSource('default');
if($this->changeDbSource('default')) {
	$this->User->create($user);
	$this->User->save($user);
}

Hope you find this useful. Happy baking.

Show me love

Close Bookmark and Share This Page

Save to Browser Favorites / Bookmarks

Email This to a Friend

Link HTML:

Permalink:

If you like this then please subscribe to the RSS Feed or Email Feed.

Post Information Categories: CakePHP, PHP, Programming
Tags: CakePHP, PHP, tutorial

Stay Connected

Subscribe to RSS Feed

Follow me on Twitter

Subscribe via e-mail

4 Responses to “How to switch between databases in CakePHP on the fly”

Tyguy7
April 27, 2010 at 8:06 pm | Permalink

OMG, thank you so much for this, I’ve been searching for days. You’d be surprised how uncommon this answer is. Or maybe not if you had to search as long as I did before you solved this on your own.
in-sanity
April 28, 2010 at 5:38 pm | Permalink

I’m glad I could help.
Roland
June 29, 2010 at 4:32 pm | Permalink

Thanks a lot for this! Really helpful.
I only have one question. In my app a user logs in and the app selects the appropriate database (here is where your example comes into action), but: Do changeDataSource and changeDbSource have to be called in every controller of the application?

Thanks a lot for sharing.
Cheers.
in-sanity
June 29, 2010 at 6:57 pm | Permalink

No necessarily. You only need to place them where you need them. I placed mine in the app_controller and app_model so that I can use them wherever I need.