Yii 1.1: Multiple Database Connection : Select database based on login user id, Dynamic

12 followers

Create a component

create a class file under protected\components named RActiveRecord.php

using this code

<?php
    class RActiveRecord extends CActiveRecord {
 
    private static $dbadvert = null;
 
    protected static function getAdvertDbConnection()
    {
 
        if (self::$dbadvert !== null)
            return self::$dbadvert;
        else
        {
             $User=User::model()->findByPk(Yii::app()->user->id);
             $db_name = $user->db_name;
 
 
             self::$dbadvert = Yii::createComponent(array(
             'class' => 'CDbConnection',
            // other config properties...
             'connectionString'=>"mysql:host=localhost;dbname=".$db_name, //dynamic database name here
              'enableProfiling' => true,
              'enableParamLogging' => true,
              'username'=>'root',
              'password'=> '', //password here
              'charset'=>'utf8',
              'emulatePrepare' => true,
              'enableParamLogging'=>true,
              'enableProfiling' => true,
             ));
            Yii::app()->setComponent('dbadvert', self::$dbadvert);
 
            if (self::$dbadvert instanceof CDbConnection)
            {   
                Yii::app()->db->setActive(false);
                Yii::app()->dbadvert->setActive(true);
                return self::$dbadvert;
            }
            else{
                throw new CDbException(Yii::t('yii','Active Record requires a "db" CDbConnection application component.'));
            }
 
        }
    }
}

Select dynamic Database name

change this line according to your use

$User=User::model()->findByPk(array('id'=>Yii::app()->user->id));
$db_name = $user->db_name

just need the database name

Extend from RActiveRecord

extend all models from RActiveRecord and call function getDbConnection()

class model-name extends RActiveRecord {
 
    public function getDbConnection()
    {
        return self::getAdvertDbConnection();
    }

Thats it!! :-)

Total 3 comments

#15198 report it
Ƒяąȼţąℓ ϻɨɲď at 2013/10/17 01:59am
I have a problem with writing on 2 database.

To create the "external DB" component, I fetch the tenant info from my master DB

public function getTenantConnection( $tenantID = null )
        {
                if ( !isset($tenantID))
                {
                        if (self::$externalTenant instanceof CDbConnection)
                        {   
                                 Yii::app()->externalTenant->setActive(false);
                                 Yii::app()->db->setActive(true);
                        }
                        return Yii::app()->tenant->db;
                } else {    
 
                        $tenant = GTBtenant::model()->findByPk($tenantID);
 
                        if ( isset($tenant))
                        {
 
                                self::$externalTenant = Yii::createComponent(array(
                                        'class' => 'CDbConnection',
 
                                        'connectionString'=>'mysql:host='.$tenant->IPaddress.';dbname='.$tenant->DBname, 
                                        'enableProfiling' => true,
                                        'enableParamLogging' => true,
                                        'username'=>$tenant->username,
                                        'password'=>$tenant->password,
                                        'charset'=>'utf8',
                                        'emulatePrepare' => true,
                                        'enableParamLogging'=>true,
                                        'enableProfiling' => true,
                                ));
                               Yii::app()->setComponent('externalTenant', self::$externalTenant);
 
                               if (self::$externalTenant instanceof CDbConnection)
                               {   
                                        Yii::app()->db->setActive(false);
                                        Yii::app()->externalTenant->setActive(true);
                                        return self::$externalTenant;
                               }
                               else{
                                        throw new CDbException(Yii::t('yii','Active Record requires a "db" CDbConnection application component.'));
                               }
                        } else {
                                        Yii::app()->alert->sendAlert( Alert::FATAL_ERROR_ALERT, 'The tenant ('. $tenantID .') couldnt be found, when requested.' );
                        }
 
 
                }

Maybe it's the problem Gerhard Liebenberg pointed it out...

it's doesn't work when I try to write on the tenant DB...

but this works like a charm:

$this->model = tenant::model()->findByPk($tenantID); 
                        $this->db = Yii::app()->dynamicDB->getDbConnection( $this->model->IPaddress,
                                                                            $this->model->DBname,
                                                                            $this->model->username,
                                                                            $this->model->password);

What did I do wrong!?

#14930 report it
Rajith R at 2013/09/23 02:21am
@Gerhard Liebenberg

good findings!!

#14920 report it
Gerhard Liebenberg at 2013/09/21 10:36am
Good work! Just be careful.

Just be careful before you start with multiple databases.

It is a great idea to have each user's data in a separate database, but if it is a commercial site, then you would probably need a separate admin database as well - which brings you to running separate databases at the same time.

If this is your situation, then AR is your only option because each model can have a separate connection string to a different database.

But DAO only use a single connection string. So you can't write your own large sql statements using multiple databases. You could however query multiple databases using separate sql statements and then merge the results.

So do careful planning before making your choice.

Good work!

Leave a comment

Please to leave your comment.

Write new article