How to Use Two Database in Yii PHP Framework

In Yii framework, using multiple databases is relatively straightforward. You can configure multiple database connections in the configuration file and then access these connections as needed throughout your application.

Recently in one of Ireland based project client has the requirement to use existing Microsoft SQL Server database and implement new feature and store data into MySQL database with Yii Framework along with multiple database connections.

Ezeelive Technologies developer has code changes in Yii framework and setup option to Use Two Databases in Yii PHP Framework.

First, we have installed and implement Microsoft SQL server driver in wamp server to make in localhost environment. Now we have to connect both the Database in Yii Framework. Following is steps to implement in Yii Framework :

1. In protected/config folder open main.php and add below code in component for MySQL and SQL server connection

'db'=>array(  // default mysql connection
 'connectionString' => 'mysql:host=localhost;port=3306;dbname=db_name',
 'emulatePrepare' => true,
 'username' => 'db_username',
 'password' => 'db_password',
 'charset' => 'utf8',
 'tablePrefix' =>'tbl_',  // if have prefix in database tables
),
'msdb'=>array( // microsoft sql server connection
 'class' => 'CDbConnection',
 'connectionString' => 'sqlsrv:Server=host_name or ip_address;Database=db_name',
 'username' => 'db_username',
 'password' => 'db_password',
 'charset' => 'GB2312',
),

2. Now SQL server connection is available in your entire application and access by CDbCommand

$company_name=Yii::app()->msdb->createCommand("SELECT TOP 1 CompanyName from Company_Details WHERE CompanyID="ezeelive")->queryRow();
if(isset($company_name) && !empty($company_name["CompanyName"])):
 echo $company_name["CompanyName"]; // it will print company name Ezeelive Technologies
else:
 echo '';
endif;

In above example we have use msdb define database connection in component

3. Sometimes we require to get the data from one table and insert into another database table, In this case, you can use createCommand to write custom loop or you can Switch database connection runtime in model class

class Company extends CActiveRecord
{
 public function init()
 {
 $this->attachEventHandler('OnBeforeSave',   array($this,'switchToWriter'));
 $this->attachEventHandler('OnAfterSave',   array($this,'switchToReader'));
 }
 public function switchToWriter()
 {
 self::$db=Yii::app()->db;
 return true;
 }
 public function switchToReader()
 {
 self::$db=Yii::app()->msdb;
 return true;
 }
 //...
}

In above example database connection will swapping means the connection will automatically change in fetching and inserting time. “switchToReader” function for fetching the data from SQL Server database and “switchToWriter” for insert/save data into MySQL database.

By following these steps, you can easily configure and use multiple databases within your Yii application. Adjust the configurations and access methods according to your specific requirements.

Please fill free to contact us or leave a comment for any type of development, integration, coding problem and help in use two databases in yii php framework. Keep Coding…!!