Multiple Database,Single Model

Please help me to solve this…

here user can create their own company.when creating company, a new database is created with default tables and a username,password is provided…all the tables and fields are same to all the databases

then i have a form with a dropdown list showing list of company name… when the user select a company i have to connect to the particular company’s database and show the login form…

i have a major database with a table

companylist (table)


companyname (fields)

databasename (fields)

i don’t know how to connect with the database dynamicaly when user choose companyname…

Let me share my ideas:

  1. Write a class (component) which will manage connections. Here is how I did it:



class ConnectionManager extends CApplicationComponent {

	

  // Represents connection pool

  private $_connections;

  

  public function getConnection($company)

  {

    // If connection hasn't been already created

    if (!isset($this->_connections[$company])) {

      // Look database name associated with this company

      $command = Yii::app()->db->createCommand('SELECT databasename FROM {companylist} WHERE companyname = :name');

      $command->bindParam(':name', $this->company, PDO::PARAM_STR);

      $databaseName = $command->queryColumn();

      // Also, you need to get username and password somehow. And adjust dsn string

      $this->_connections[$company] = new CDbConnection("mysql:host=localhost;dbname=$databaseName", $username, $password);

      $this->_connections[$company]->active = true;

    }

    return $this->_connections[$company];

  }

}



You didn’t specify where could we take db user/pass from. Also as mentioned in comments, you have to adjust your dsn to fit your database settings.

  1. Extend CActiveRecord to work with different databases depending on company context:



class MultiDatabaseActiveRecord extends CActiveRecord {

  

  // Represents the company

  public $company;

  

  public function getDbConnection()

  {

    // If company is not set behave like normal CActiveRecord

    if (is_null($this->company)) {

    	return parent::getDbConnection();

    }

    // Otherwise get connection for this AR instance

    return Yii::app()->connectionManager->getConnection($company);

  }

  

}



Pretty simple here, we override getDbConnection() to provide required connection. Also, we use our ConnectionManager component here.

  1. Configure your application to use component:



'components' => array(

  'connectionManager' => array(

  	'class' => 'application.components.ConnectionManager',

  )

)



So it will be accessible through Yii::app()->connectionManager.

  1. Use it



public function actionLogin($company)

{

  $user = new User('login');

  $user->company = $company;

  // do your normal operations like login, validate

}



The method above will be in one of your controllers. It sets the company property of AR, company name is taken from $_GET or $_POST. Of course you should check if company with such name exist, if not present 404 and so on.

Thanks for your reply… let me try and told you…

Why not to use a single database, and a table filtered by user id?

This is a more simple solution.

Anyway, there are many thread (eg this, this and this) about connection to different database.

yes,you are right… but it is quite large application… thats y i choose to create different database for each company…

Thanks for your code… every model in the company database should extend MultiDatabaseActiveRecord instead of CActiveRecord right… it works well