How to Use Two Database in Yii PHP Framework

Back to Blog
yii framework developer india - ezeelive

How to Use Two Database in Yii PHP Framework

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.

Please fill free to contact us or leave a comment for any type of development, integration and coding problem. Keep Coding…!!

Summary
How to Use Two Database in Yii PHP Framework
Article Name
How to Use Two Database in Yii PHP Framework
Description
Connect two database microsoft sql server and mysql, how to use two database in yii php framework, multiple database with yii framework
Author
Publisher Name
Ezeelive Technologies
Publisher Logo

Share this post

Comment (1)

  • Avatar
    Valeria Reply

    Way cοol! Some extremеly valid points! I ɑppreciate you penning tɦis article plus the rest of the websіte is extremely good.

    March 11, 2014 at 3:50 pm

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Back to Blog