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 ...

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...

Android Application Development In Mumbai

Android is quite busy in conquering the mobile application development market and there are plenty of companies which have headed towards android app development projects. It is an open source platform. The popularity of the Android is increasing rapidly and plenty of web developers are trying to make best use of the mobile platform. Android app developer Mumbai has amazed the entire world with their marvelous success in the mobile application industry. The android apps created are highly reliable and robust in nature when compared to other platforms. Tools and technologies used in android app development Most of the mobile app developers rely on Java language. With the recent developments on the Android platform, the developers have started developing the application with the support of the latest version of Adobe Air technologies and Adobe flash. Android SDK is used by the developers in mobileapp development in mumbai for writing code, testing and debugging the apps. ...