Cakephp switch database connection in controller for CakePHP 2.2.5

Question

I am trying to get data from two different databases in a controller

app/Controller/UsersController.php

my db connections are declared in the database.php

$default = array(
    ...
    'database' => 'test'
    ...
    );
$test = array(
    ...
    'database' => 'test1'
    ...
    );

and in my display() action:

public function display() {
    $this->set('defaultUsers', $this->User->find('all'));
    $this->User->schemaName = 'test1';
    $this->User->cacheQueries = false;
    $this->set('testUsers', $this->User->find('all'));
}

This would allow me to grab data from two different sources successfully, however problem is that these two databases have to have the same password otherwise it wouldn't work.

I've tried other solutions found here and other sites. like:

• changing $this->User->useDbConfig = 'test' and $this->User->cacheQueries = false would still give me the same dataset;

• using ConnectionManager::getDataSource() and setConfig(), create(), drop(), setDataSource(), etc. None of those worked and some don't even exist any more.

Any help would be greatly appreciated! As I need to the same codebase for two similar applications a.k.a two databases.

Thanks!

Answer 1

You probably need to use 'setDataSource()' to switch the datasource/connection as this will reset the 'cached' schema etc on the Model;

public function display() {
    $this->set('defaultUsers', $this->User->find('all'));
    $this->User->setDataSource('test1');
    $this->set('testUsers', $this->User->find('all'));
}

If you need to access data from both databases throughout your application, another option is to create two User models, one with 'test' as datasource, and another that uses 'test1' as datasource. This way you don't have to switch the data-source all the time

Example:

class TestUser extends AppModel {
    public $useTable = 'users'; // name of the database table 
    public $useDbConfig = 'test1';  // name of the database configuration in database.php
}

On a further note: The '$test' database-config is pre-configured database-connection that is used for running CakePHP Unit-tests. You might consider creating your own database-connection name