tip: multiple db connection solution

There are a few posts on multiple db connection solutions here and there. But none of them provide a real example. I here offer a real example to show how to setup multiple db connections.

Special thanks to Qiang.

First, setup a second DB connection in config/main.php like the following, where [color=red]dbhit[/color] is the second db connection.

Now, you want to setup a model with the second dbconnection (dbhit).

As per Qiang's suggestion, we create a class file [color=red]models/HActiveRecord.php[/color] (name it as whatever you prefer).

This file looks like

Notice I have [color=red]public static $dbhit

[/color] here. This is very important as this allows you to access two dababases simultaneously. Otherwise, if you use $db, Yii will OVERRIDE the existing DB connection. If you have some menus that are based on the first db connection, your system will run erratic.

now, use your favoriate Yiic Shell to create an appropriate model. In my case, I created a model Hitcounter.php

In the new Hitcounter.php model class, change from the following:

to

Now, create your controller just like normal.

So to summarize:

1, we first add a second database connection in config/main.php

2, create an extension of CActiveRecord.php and name it accordingly (such as HActiveRecord)

3, Create a new model based on this extended CActiveRecord.php (such as HActiveRecord).

If you only have one model, you can also combine step 2 and 3 together.

Cheers,

dear moho,

i follow ur steps to connect different db by diff connection string, but have some error prompt

Step1:

~insert new connection string into /config/main.php, show as below:



		'db'=>array(


			// 'connectionString'=>'sqlite:'.dirname(__FILE__).'/../data/blog.db',


			 // uncomment the following to use MySQL as database


			'connectionString'=>'mysql:host=localhost;dbname=test',


			'username'=>'root',


			'password'=>'',


			// */


		),


		


		'Test_db'=>array(


			// 'connectionString'=>'sqlite:'.dirname(__FILE__).'/../data/blog.db',


			 // uncomment the following to use MySQL as database


			'connectionString'=>'mysql:host=192.168.1.85;dbname=test',


			'username'=>'root',


			'password'=>'',


			// */


		),		


Step 2:

~ create a new php in "C:\xampp\htdocs\yii\demos\blog\protected\models" folder name HActiveRecord.php

~the modify code show as below



<?php


class HActiveRecord extends CActiveRecord


{


   public static $Test_db;


   public function getDbConnection()


   {


      if(self::$Test_db!==null)


         return self::$Test_db;


      else


      {


         self::$Test_db=Yii::app()->Test_db;


		 echo self::$Test_db->connectionString;


		 


         if(self::$Test_db instanceof CDbConnection)


         {


            self::$Test_db->setActive(true);


            return self::$Test_db;


         }


         else


            throw new CDbException(Yii::t('yii','Active Record requires a "db" CDbConnection application component.'));


      }


   }





}


?>





Step 3:

~ I want my "Comment" model connect with new connection string "Test_db"

so i change the "C:\xampp\htdocs\yii\demos\blog\protected\models\Comment.php"

~the modify codes show as below:



<?php





class Comment extends HActiveRecord


{


	const STATUS_PENDING=0;


	const STATUS_APPROVED=1;


	/**


	 * @var string this property is used to collect user verification code input


	 */


	public $verifyCode;





	/**


	 * Returns the static model of the specified AR class.


	 * @return CActiveRecord the static model class


	 */


	public static function model($className=__CLASS__)


	{


		return parent::model($className);


	}





	/**


	 * @return string the associated database table name


	 */


	public function tableName()


	{


		return 'Comment';


	}





	/**


	 * @return array validation rules for model attributes.


	 */


	public function rules()


	{


		return array(


			array('author,email,url','length','max'=>128),


			array('author,email,content', 'required'),


			array('email','email'),


			array('url','url'),


			array('verifyCode', 'captcha', 'on'=>'insert', 'allowEmpty'=>!Yii::app()->user->isGuest || !extension_loaded('gd')),


		);


	}





	/**


	 * @return array relational rules.


	 */


	public function relations()


	{


		return array(


			'post'=>array(self::BELONGS_TO, 'Post', 'postId', 'joinType'=>'INNER JOIN'),


		);


	}





	/**


	 * @return array customized attribute labels (name=>label)


	 */


	public function attributeLabels()


	{


		return array(


			'author'=>'Name',


			'url'=>'Website',


			'content'=>'Comment',


			'verifyCode'=>'Verification Code',


		);


	}





	/**


	 * @return array attributes that can be massively assigned


	 */


	public function safeAttributes()


	{


		return array(


			'author',


			'email',


			'url',


			'content',


			'verifyCode',


		);


	}





	/**


	 * @return array comment status names indexed by status IDs


	 */


	public function getStatusOptions()


	{


		return array(


			self::STATUS_PENDING=>'Pending',


			self::STATUS_APPROVED=>'Approved',


		);


	}





	/**


	 * @return string the status display for the current comment


	 */


	public function getStatusText()


	{


		$options=$this->statusOptions;


		return isset($options[$this->status]) ? $options[$this->status] : "unknown ({$this->status})";


	}





	/**


	 * @return string the hyperlink display for the current comment's author


	 */


	public function getAuthorLink()


	{


		if(!empty($this->url))


			return CHtml::link(CHtml::encode($this->author),$this->url);


		else


			return CHtml::encode($this->author);


	}





	/**


	 * @return integer the number of comments that are pending approval


	 */


	public function getPendingCommentCount()


	{


		return Comment::model()->count('status='.self::STATUS_PENDING);


	}





	/**


	 * @param integer the maximum number of comments that should be returned


	 * @return array the most recently added comments


	 */


	public function findRecentComments($limit=10)


	{


		$criteria=array(


			'condition'=>'Comment.status='.self::STATUS_APPROVED,


			'order'=>'Comment.createTime DESC',


			'limit'=>$limit,


		);


		return $this->with('post')->findAll($criteria);


	}





	/**


	 * Approves a comment.


	 */


	public function approve()


	{


		if($this->status==Comment::STATUS_PENDING)


		{


			$this->status=Comment::STATUS_APPROVED;


			$this->save();


			Post::model()->updateCounters(array('commentCount'=>1), "id={$this->postId}");


		}


	}





	/**


	 * Prepares attributes before performing validation.


	 */


	protected function beforeValidate($on)


	{


		$parser=new CMarkdownParser;


		$this->contentDisplay=$parser->safeTransform($this->content);


		if($this->isNewRecord)


			$this->createTime=time();


		return true;


	}





	/**


	 * Postprocessing after the record is saved


	 */


	protected function afterSave()


	{


		if($this->isNewRecord && $this->status==Comment::STATUS_APPROVED)


			Post::model()->updateCounters(array('commentCount'=>1), "id={$this->postId}");


	}





	/**


	 * Postprocessing after the record is deleted


	 */


	protected function afterDelete()


	{


		if($this->status==Comment::STATUS_APPROVED)


			Post::model()->updateCounters(array('commentCount'=>-1), "id={$this->postId}");


	}


}


After apply step above i try to run the program it show error message below



CException


Description


Object configuration must be an array containing a "class" element.





Source File


C:xampphtdocsyiidemosblogprotectedmodelsHActiveRecord.php(11)





00001: <?php


00002: class HActiveRecord extends CActiveRecord


00003: {


00004:    public static $dbComment;


00005:    public function getDbConnection()


00006:    {


00007:       if(self::$dbComment!==null)


00008:          return self::$dbComment;


00009:       else


00010:       {


00011:          self::$dbComment=Yii::app()->dbComment;


00012:          // echo self::$dbComment->connectionString;


00013:          


00014:          if(self::$dbComment instanceof CDbConnection)


00015:          {


00016:             self::$dbComment->setActive(true);


00017:             return self::$dbComment;


00018:          }


00019:          else


00020:             throw new CDbException(Yii::t('yii','Active Record requires a "db" CDbConnection application component.'));


00021:       }


00022:    }


00023: 


Stack Trace


#0 C:xampphtdocsyiiframeworkbaseCModule.php(352): YiiBase::createComponent(Array)


#1 C:xampphtdocsyiiframeworkbaseCModule.php(91): CModule->getComponent('dbComment')


#2 C:xampphtdocsyiidemosblogprotectedmodelsHActiveRecord.php(11): CModule->__get('dbComment')


#3 C:xampphtdocsyiiframeworkdbarCActiveRecord.php(2177): HActiveRecord->getDbConnection()


#4 C:xampphtdocsyiiframeworkdbarCActiveRecord.php(645): CActiveRecordMetaData->__construct(Object(Comment))


#5 C:xampphtdocsyiiframeworkdbarCActiveRecord.php(658): CActiveRecord::model('Comment')


#6 C:xampphtdocsyiiframeworkdbarCActiveRecord.php(392): CActiveRecord->getMetaData()


#7 C:xampphtdocsyiidemosblogprotectedcontrollersPostController.php(234): CActiveRecord->__construct()


#8 C:xampphtdocsyiidemosblogprotectedcontrollersPostController.php(82): PostController->newComment(Object(Post))


#9 C:xampphtdocsyiiframeworkwebactionsCInlineAction.php(32): PostController->actionShow()


#10 C:xampphtdocsyiiframeworkwebCController.php(300): CInlineAction->run()


#11 C:xampphtdocsyiiframeworkwebCController.php(278): CController->runAction(Object(CInlineAction))


#12 C:xampphtdocsyiiframeworkwebCController.php(257): CController->runActionWithFilters(Object(CInlineAction), Array)


#13 C:xampphtdocsyiiframeworkwebCWebApplication.php(332): CController->run('show')


#14 C:xampphtdocsyiiframeworkwebCWebApplication.php(120): CWebApplication->runController('post/show')


#15 C:xampphtdocsyiiframeworkbaseCApplication.php(133): CWebApplication->processRequest()


#16 C:xampphtdocsyiidemosblogindex.php(13): CApplication->run()


#17 {main}2009-06-25 07:24:59 Apache/2.2.9 (Win32) DAV/2 mod_ssl/2.2.9 OpenSSL/0.9.8i mod_autoindex_color PHP/5.2.6 Yii Framework/1.0.6


Please advised

Thanks and HAVE A NICE DAY

martial

In your Test_db app config, you should specify 'class'=>'CDbConnection'

(You don't need this for 'db' because it is predefined.)

Qiang is so fast! Martial, you should follow Qiang's suggestion.

That’s my careless mistake,  ;D

it work after include "'class'=>'CDbConnection',"

Thanks Qiang and moho

hi

i think you have an error on the red line

anyway thanks for this, just what I need!

Hi Moho, I was trying to read your solution for multiple databases but on your post I can read no code.

I.e. your post appears like this to me:

"Special thanks to Qiang.

First, setup a second DB connection in config/main.php like the following, where dbhit is the second db connection.

EMPTY

Now, you want to setup a model with the second dbconnection (dbhit)."

I’ve tried to follow martial123’s response, since i guess he based everything on his original post, but since he got an error I’m not sure of where he went wrong. Could you help me out with this.

I appreciate your help in advance,

Thanks for this how-to but how do you deal with a situation where you have multiple databases (with same schema) and the number of database instances grow dynamically? I am working on a project where I have to add database instances for different groups as new groups get created. The database schema is the same but I have to create new instances of the schema dynamically and use the new instance?

Thanks

I’ve just posted an extension to help you to use multiple databases in your models

Its located here if interests you