CakePHP switch database (using same datasource) on the fly?

Question

I have tried to build a small function that would be use in controller to switch database on the fly, i must use only one datasource.

On my database.php :

function __construct() {

    $server = Configure::read('Server');

    if(!empty($server['database'])) $this->local['database'] = $server['database'];
    $this->default = $this->{$server['datasource']};

}

Is used to switch database depending on server config. It is working great.

I tried to build up this :

/**
 * Connects to specified database
 *
 * @param array $config Server config to use {datasource:?, database:?}
 * @return array db->config on success, false on failure
 * @access public
 */
function dbConnect($config = array()) {
    ClassRegistry::init('ConnectionManager');
    //debug($config['datasource']);
    //$dbInstance =& ConnectionManager::getInstance();
    //$dbInstance->config->{$config['datasource']}['database'] = $config['database'];

    $db =& ConnectionManager::getDataSource($config['datasource']);
    $db->disconnect();
    $db->cacheSources = false;
    $db->config['database'] = $config['database'];
    $db->config['persistent'] = false;
    debug($db->config);
    $db->connect();
    if(!$db->isConnected()) {
        $this->error('!$db->isConnected()');
        return false;
    }
    return $db->config;
}

But sadly, everything seems to work but i alwas get data from the same DB using $this->Player->find('list') for instance. I tried $this->Player->cacheQueries = false; with no more success.

Solution 1

Made it work using this (create a new connection on the fly) :

$newDbConfig = $this->dbConnect($serverConfig);
$this->Model->useDbConfig = $newDbConfig['name'];
$this->Model->cacheQueries = false;

With :

/**
 * Connects to specified database
 *
 * @param array $config Server config to use {datasource:?, database:?}
 * @return array db->config on success, false on failure
 * @access public
 */
function dbConnect($config = array()) {
    ClassRegistry::init('ConnectionManager');

    $nds = $config['datasource'] . '_' . $config['database'];
    $db =& ConnectionManager::getDataSource($config['datasource']);
    $db->setConfig(array('name' => $nds, 'database' => $config['database'], 'persistent' => false));
    if($ds = ConnectionManager::create($nds, $db->config)) return $db->config;
    return false;

}

Solution 2

I just wanted to tell that nowadays it's really simple to change current model's datasource (database connection):

For example in my PersonsController index:

public function index() {

    //change to a different datasource which is already written in App/Config/database.php-file
    $this->Person->setDataSource('testdb2');
    $persons = $this->Person->find('all');
    $this->set('db1persons', $persons);

    //change to a different database
    $this->Person->setDataSource('testdb');
    $persons = $this->Person->find('all');
    $this->set('db2persons', $persons);
}

As you can see the key here is to use $this->Model->setDataSource()

Comments

[Ariaan]

Never mind, I figured it out! It's All in the Controller! This is wonderfull, great you saved my day... a year a go ;) THANK YOU! Here's my question about the subject.

[Ariaan]

I'm afraid, this wont work dynamically. If you have 1 database per client, this is not what you want.

[Anupal]

Urgent: I am having issue while connecting each db's in foreach loop one by one. It selects 1st database in each loop. Is it the cache issue? Please help!