Yii multiple database connections

Question

I have a three databases, every database has the same table with the same fields, but I don't know how to get all records from all of three databases at the same time in Yii.

Please help

Solution 1

1.We will start with configuring DB connections. Open protected/config/main.php and define a primary connection as described in the guide:

'db'=>array(
        'connectionString' => 'mysql:host=localhost;dbname=db1',
        'emulatePrepare' => true,
        'username' => 'root',
        'password' => '',
        'charset' => 'utf8',
    ),

2.Then copy it, rename the 'db' component to 'db2' and change the connection string accordingly. Also, you need to add the class name as follows:

'db2'=>array(
    'class'=>'CDbConnection',
    'connectionString' => 'mysql:host=localhost;dbname=db2',
    'emulatePrepare' => true,
    'username' => 'root',
    'password' => '',
    'charset' => 'utf8',
),

3.Then copy it, rename the 'db' component to 'db3' and change the connection string accordingly. Also, you need to add the class name as follows:

'db2'=>array(
    'class'=>'CDbConnection',
    'connectionString' => 'mysql:host=localhost;dbname=db2',
    'emulatePrepare' => true,
    'username' => 'root',
    'password' => '',
    'charset' => 'utf8',
),

4.That is it. Now, you have two database connections and can use them with DAO and query builder as follows:

$db1Rows = Yii::app()->db->createCommand($sql)->queryAll();
$db2Rows = Yii::app()->db2->createCommand($sql)->queryAll();
$db3Rows = Yii::app()->db2->createCommand($sql)->queryAll();

Solution 2

Just add new database connection to config/main.php

    'db1'=>array(
        'connectionString' => 'mysql:host=localhost;dbname=database1',
        'emulatePrepare' => true,
        'username' => 'root',
        'password' => '',
        'charset' => 'utf8',
    ),

    'db2'=>array(
        'connectionString' => 'mysql:host=localhost;dbname=database2',
        'username' => 'root',
        'password' => '',
        'class'=>'CDbConnection',          // DO NOT FORGET THIS!
    ),

and now you can connect to your database like this:

Yii::app()->db1 ...
Yii::app()->db2 ...

check this article to more explaining:

http://www.yiiframework.com/wiki/123/multiple-database-support-in-yii/

and this:

http://www.yiiframework.com/wiki/78/multiple-databases-and-multiple-domains/