Multiple Database Connection In Yii 1.0

Yii have the multiple database connection by default. Yii components is used to make the relation between our models and database. To use the multiple database we have to configure the “protected/config/main.php” file. First database connection information is called “db” and second database connection is called “seconddb”.

Configure Multiple DB

In main.php

'components'=>array(
	'db'=>array(
		'connectionString' => 'mysql:host=localhost;dbname=testdrive',
		'emulatePrepare' => true,
		'username' => 'root',
		'password' => '',
		'charset' => 'utf8',
	),
    'secondaryDb'=>array(
		'seconddb'=>array(
			'connectionString' => 'mysql:host=localhost;dbname=seconddb',
			'emulatePrepare' => true,
			'username' => 'root',
			'password' => '',
			'charset' => 'utf8',
			'class'=>'CDbConnection'
		),
    ),
)

We must have to add ‘class’=>’CDbConnection’ in components and create the models for two database.

Method 1

Override the DB Connection

CActiveRecord has a method ‘getAdvertDbConnection()’ is used to get the database connection. If we override this function, we can use the second database. We have to place this function in model wherever we want to access the second database.

getAdvertDbConnection() Method

<?php
class seconddbActiveRecord extends CActiveRecord {
    private static $dbadvert = null;
 
    protected static function getAdvertDbConnection()
    {
 
        if (self::$dbadvert !== null)
            return self::$dbadvert;
        else
        {
             self::$dbadvert = Yii::createComponent(array(
             'class' => 'CDbConnection',
            // other config properties...
//dynamic database name here
             'connectionString'=>"mysql:host=localhost;dbname=databasename", 
              'enableProfiling' => true,
              'enableParamLogging' => true,
              'username'=>'root',
              'password'=> '$123', //password here
              'charset'=>'utf8',
              'emulatePrepare' => true,
              'enableParamLogging'=>true,
              'enableProfiling' => true,
             ));
            Yii::app()->setComponent('dbadvert', self::$dbadvert);
 
            if (self::$dbadvert instanceof CDbConnection)
            {   
                Yii::app()->db->setActive(false);
                Yii::app()->dbadvert->setActive(true);
                return self::$dbadvert;
            }
            else{
                throw new CDbException(Yii::t('yii','Active Record requires a "db" CDbConnection application component.'));
            }
 
        }
    }
}
class ProjectModel extends seconddbActiveRecord {
    public function getDbConnection()
    {
        return self::getAdvertDbConnection();
    }   
    .....
}

Method 2

$firstDatabase = Yii::app()->db;
$firstDatabase->active = true;
// etc, then
$secondDatabase = Yii::app()->seconddb;
$secondDatabase->active = true;
class ProjectModel extends CActiveRecord {
private $seconddb = null;
 
protected function getDbConnection()
{
    if (self::$seconddb !== null)
        return self::$seconddb;
    else
    {
        self::$seconddb = Yii::app()->seconddb;
        if (self::$anotherDb instanceof CDbConnection)
        {
            self::$seconddb->setActive(true);
            return self::$seconddb;
        }
        else
            throw new CDbException(Yii::t('yii',
			'Active Record requires CDbConnection Component.'));
    }
}