Multiple database connections and Yii 2.0

Question

I have two databases, and every database has the same table with the same fields, but how do I get all records from all of two databases at the same time in Yii 2.0?

Answer 1

First you need to configure your databases like below:

return [ 'components' => [     'db1' => [         'class' => 'yii\db\Connection',         'dsn' => 'mysql:host=localhost;dbname=db1name', //maybe other dbms such as psql,...         'username' => 'db1username',         'password' => 'db1password',     ],     'db2' => [         'class' => 'yii\db\Connection',         'dsn' => 'mysql:host=localhost;dbname=db2name', // Maybe other DBMS such as psql (PostgreSQL),...         'username' => 'db2username',         'password' => 'db2password',     ], ], ]; 

Then you can simply:

// To get from db1 Yii::$app->db1->createCommand((new \yii\db\Query)->select('*')->from('tbl_name'))->queryAll()  // To get from db2 Yii::$app->db2->createCommand((new \yii\db\Query)->select('*')->from('tbl_name'))->queryAll() 

If you are using an active record model, in your model you can define:

public static function getDb() {     return Yii::$app->db1; }  //Or db2 public static function getDb() {     return Yii::$app->db2; } 

Then:

If you have set db1 in the getDb() method, the result will be fetched from db1 and so on.

ModelName::find()->select('*')->all(); 

Answer 2

Just to add: I followed the answer provided but still got an error: "Unknown component ID: db"

After some testing, here is what I discovered: The function getDB is only called AFTER a connection is made to db. Therefore, you cannot delete or rename 'db' in the config file. Instead, you need to let the call to 'db' proceed as normal and then override it afterwards.

The solution (for me) was as follows:

In config/web.php add your second database configuration below db as follows:

'db' => require(__DIR__ . '/db.php'), 'db2' => [     'class' => 'yii\db\Connection',     'dsn' => 'mysql:host=localhost;dbname=name',     'username' => 'user',     'password' => 'password',     'charset' => 'utf8',     'on afterOpen' => function ($event) {         $event->sender->createCommand("SET time_zone = '+00:00'")->execute();     }, ], 

DO NOT rename db. Failure to find db will cause an error. You can name db2 whatever you like.

Now in the model, add the following code:

class ModelNameHere extends \yii\db\ActiveRecord {     // add the function below:    public static function getDb() {        return Yii::$app->get('db2'); // second database    } 

This will now override the default db configuration.

I hope that helps somebody else.

Note: you can include the configuration for db2 in another file but you cannot include it in the db.php file (obviously). Instead, create a file called db2.php and call it as you do db:

'db' => require(__DIR__ . '/db.php'),     'db2' => require(__DIR__ . '/db2.php'), 

Thanks