Skip to main content

How to Use Two Database in Yii PHP Framework

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

First we have install 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. Sometime 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 on info@ezeelive.com or leave a comment for any type of development, integration and coding problem. Keep Coding...!!

Comments

Popular posts from this blog

Overcoming Challenges in Adopting Generative AI in India

Adopting Generative AI in India presents several challenges, but it also offers significant opportunities for growth across various sectors. Here are some key obstacles and strategies to overcome them: 1. Infrastructure and Resource Limitations Challenge : India still faces issues with the availability of high-performance computing resources needed for generative AI models. This includes access to powerful GPUs and cloud services, which can be expensive. Solution : Partnerships with global tech firms can help bring advanced infrastructure to India. Government initiatives like the National AI Strategy and collaboration with research institutions can also boost local capabilities. 2. Data Privacy and Security Concerns Challenge : Data security is a significant concern, especially with the collection and usage of sensitive information. India's data protection laws, such as the Personal Data Protection Bill, are still evolving. Solution : Clear regulatory frameworks around data usage ...

Check and Use GZIP Compression through PHP

gzip compression is using for increase website speed and save server bandwidth. Read the below points to add gzip compression in your website: 1. Ask Hosting server provider to enable  gzip compresssion . 2. Compress all the css and js file using  7-zip  e.g. if your css file name is style.css it will save as style.css.gz. 3. Upload all the file on live server and set Content Encode GZIP to .gz files 4. Define  gzip global variable  in your common php file (remember the common file will include in all the php file into your website) $gzip_string=""; if (substr_count($_SERVER["HTTP_ACCEPT_ENCODING"], "gzip")) { $gzip_string=".gz"; }else{ $gzip_string=""; } 5. Put defined variable in all your script and style including in your php pages e.g. <link rel="stylesheet" type="text/css" href="css/my-style-file.css<?php echo $gzip_string;?>" /> <script type="text/javascript" ...

Why Indian PHP Development Companies Deliver Cost-Effective Solutions

In the world of web development, PHP remains one of the most popular programming languages, powering a vast majority of websites and applications. Businesses across the globe seek PHP Development Services to build scalable, secure, and high-performing web solutions. Indian PHP development companies have gained global recognition for delivering cost-effective solutions without compromising on quality. But what makes India a preferred destination for PHP development? Let’s explore the factors that contribute to the cost-effectiveness of Indian PHP development companies. 1. Competitive Labor Costs One of the primary reasons Indian PHP development companies can offer cost-effective solutions is the competitive labor cost. India has a vast talent pool of skilled PHP developers who are available at a fraction of the cost compared to developers in Western countries such as the United States, the United Kingdom, and Australia. This cost advantage allows businesses to allocate budgets more eff...