Dynamic Database Connection with CakePHP

Recently I was working on a project where I had a central Cake application for a group of clients and each client had their own database.

I wanted to have all users sign into a central user’s table associated with the application, but then for the bulk of the controllers in the app have the database config change based on a field in the user record.

So if Bob Smith from Company A signs in to our app, he needs to use database configuration ‘company_a’.

Since we are using Cake’s built in Auth component, we always have access to the user record in the Session from the controller. But for the life of me, I could not figure out how to get at the Session data from the Model!

I saw several comments in my search that said to just abandon the Cake MVC model and get what I needed from $_SESSION. I had no qualms about doing that, but $_SESSION had no data. I believe this is because Cake overrides PHP’s default session handlers so session data stored by Cake is not going to end up in $_SESSION. Feel free to comment if I am wrong about that.

So I had the data I needed in the controller, but at that point the model has already been instantiated.

But it became a timing issue — ‘mydatabase’ had to be a valid database connection before the controller is even instantiated.

So to fix, a ‘dummy’ database was needed to make the initial connection, then we can make our adjustments in the controller.

function beforeFilter() {
     $config = array(
          'login'    => 'myusername',
          'password' => 'mypassword',
          'database' => $this->Auth->user('company_name')
     );
 
     $db =& ConnectionManager::getDataSource("mydatabase");
     $db->disconnect();
     $db->setConfig($config);
     $db->connect();
}

1 Comment »

1. OK, this might be a bit late, but you can also use the following code in your model

   $newDb =& ConnectionManager::create('dynamic_db', 
   array(
       'driver' =>  'mysql',
       'host' => 'local',
       'login' => 'user',
       'password' => 'pass',
       'database' => 'my_db'));
    
   $this->useDb = 'dynamic_db';

   as per https://dev.cakephp.org/ticket/1068

   Comment by Arno Esterhuizen — May 13, 2010 @ 7:35 am

RSS feed for comments on this post. TrackBack URI

Leave a comment

Line and paragraph breaks automatic, e-mail address never displayed, HTML allowed: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre lang="" line="" escaped="">