Change Database connection at runtime in Codeigniter

Question

I want to connect to a different database based on the sub domain, Currently I've this code, but it not looking a decent solution to the problem. Please guide in right direction what is the best way to achieve this in code igniter.

class DBConnection extends CI_Model
{
    public function __construct()
    {
        parent::__construct();
    }

    public function dbConfig(){

        $efg="";
        $data = explode('.',$_SERVER['SERVER_NAME']);
        if (!empty($data[0])) {
            $efg = $data[0];
        }

        $sql="SELECT DbUsername,DbName,DbPassword FROM abc WHERE efg=?";
        $d_result=$this->db->query($sql,array($efg))->result_array();

        $this->db->close();

        $config['hostname'] = "localhost";
        $config['username'] = $d_result[0]["DbUsername"];
        $config['password'] = $d_result[0]["DbPassword"];
        $config['database'] = $d_result[0]["DbName"];
        $config['dbdriver'] = "mysql";
        $config['dbprefix'] = "";
        $config['pconnect'] = FALSE;
        $config['db_debug'] = TRUE;
        $config['cache_on'] = FALSE;
        $config['cachedir'] = "";
        $config['char_set'] = "utf8";
        $config['dbcollat'] = "utf8_general_ci";
        return $config;
    }
} 

Then in User model class, I've something like this, which is on each & every request doing all the above processing again. I want the switch will take place in the start and further queries will push towards selected database.

class User extends CI_Model
{
    private $clientDB;

    public function __construct()
    {
        parent::__construct();
        $db=new DBConnection();
        $this->clientDB=$this->load->database($db->dbConfig(),TRUE);
    }

    public function isUserExists($username,$password)
    {
        $sql="SELECT uid FROM `aaa` WHERE `uname`=? AND `upwd`=?";
        $d_result=$this->clientDB->query($sql,array($username,$password));
        return $d_result;
    }
} 

Solution

I would personally just run a PDO query in the /application/config/database.php file like this:

Approach #1

<?php  if ( ! defined('BASEPATH')) exit('No direct script access allowed');
/*
| -------------------------------------------------------------------
| DATABASE CONNECTIVITY SETTINGS
| -------------------------------------------------------------------
| This file will contain the settings needed to access your database.
|
| For complete instructions please consult the 'Database Connection'
| page of the User Guide.
|
| -------------------------------------------------------------------
| EXPLANATION OF VARIABLES
| -------------------------------------------------------------------
|
|   ['hostname'] The hostname of your database server.
|   ['username'] The username used to connect to the database
|   ['password'] The password used to connect to the database
|   ['database'] The name of the database you want to connect to
|   ['dbdriver'] The database type. ie: mysql.  Currently supported:
                 mysql, mysqli, postgre, odbc, mssql, sqlite, oci8
|   ['dbprefix'] You can add an optional prefix, which will be added
|                to the table name when using the  Active Record class
|   ['pconnect'] TRUE/FALSE - Whether to use a persistent connection
|   ['db_debug'] TRUE/FALSE - Whether database errors should be displayed.
|   ['cache_on'] TRUE/FALSE - Enables/disables query caching
|   ['cachedir'] The path to the folder where cache files should be stored
|   ['char_set'] The character set used in communicating with the database
|   ['dbcollat'] The character collation used in communicating with the database
|                NOTE: For MySQL and MySQLi databases, this setting is only used
|                as a backup if your server is running PHP < 5.2.3 or MySQL < 5.0.7
|                (and in table creation queries made with DB Forge).
|                There is an incompatibility in PHP with mysql_real_escape_string() which
|                can make your site vulnerable to SQL injection if you are using a
|                multi-byte character set and are running versions lower than these.
|                Sites using Latin-1 or UTF-8 database character set and collation are unaffected.
|   ['swap_pre'] A default table prefix that should be swapped with the dbprefix
|   ['autoinit'] Whether or not to automatically initialize the database.
|   ['stricton'] TRUE/FALSE - forces 'Strict Mode' connections
|                           - good for ensuring strict SQL while developing
|
| The $active_group variable lets you choose which connection group to
| make active.  By default there is only one group (the 'default' group).
|
| The $active_record variables lets you determine whether or not to load
| the active record class
*/

$active_group = 'default';
$active_record = TRUE;

$efg="";
$data = explode('.',$_SERVER['SERVER_NAME']);
if (!empty($data[0])) {
    $efg = $data[0];
}

$dbh = new PDO(YourPDOConnDetails);

$sql = "SELECT DbUsername,DbName,DbPassword FROM abc WHERE efg=?";

$sth = $dbh->prepare($sql);
$sth->execute(array($efg));
$d_result= $sth->fetchAll(PDO::FETCH_ASSOC);

// We are done with PDO for this purpose so free up some resources!
$dbh = null;
unset($dbh);

$db['default']['hostname'] = 'localhost';
$db['default']['username'] = $d_result[0]["DbUsername"];
$db['default']['password'] = $d_result[0]["DbPassword"];
$db['default']['database'] = $d_result[0]["DbName"];
$db['default']['dbdriver'] = 'mysql';
$db['default']['dbprefix'] = '';
$db['default']['pconnect'] = FALSE;
$db['default']['db_debug'] = TRUE;
$db['default']['cache_on'] = FALSE;
$db['default']['cachedir'] = '';
$db['default']['char_set'] = 'utf8';
$db['default']['dbcollat'] = 'utf8_general_ci';
$db['default']['swap_pre'] = '';
$db['default']['autoinit'] = TRUE;
$db['default']['stricton'] = FALSE;


/* End of file database.php */
/* Location: ./application/config/database.php */

Approach #2

$efg="";
$data = explode('.',$_SERVER['SERVER_NAME']);
if (!empty($data[0])) {
    $efg = $data[0];
}

$active_group = $efg; // this will choose from subdomain1/subdomain2 settings below. Add as many as you need
$active_record = TRUE;

$db['subdomain1']['hostname'] = 'localhost';
$db['subdomain1']['username'] = 'subd1User';
$db['subdomain1']['password'] = 'subd1Pass';
$db['subdomain1']['database'] = 'subd1DBName';
$db['subdomain1']['dbdriver'] = 'mysql';
$db['subdomain1']['dbprefix'] = '';
$db['subdomain1']['pconnect'] = FALSE;
$db['subdomain1']['db_debug'] = TRUE;
$db['subdomain1']['cache_on'] = FALSE;
$db['subdomain1']['cachedir'] = '';
$db['subdomain1']['char_set'] = 'utf8';
$db['subdomain1']['dbcollat'] = 'utf8_general_ci';
$db['subdomain1']['swap_pre'] = '';
$db['subdomain1']['autoinit'] = TRUE;
$db['subdomain1']['stricton'] = FALSE;

$db['subdomain2']['hostname'] = 'localhost';
$db['subdomain2']['username'] = 'subd2User';
$db['subdomain2']['password'] = 'subd2Pass';
$db['subdomain2']['database'] = 'subd2DBName';
$db['subdomain2']['dbdriver'] = 'mysql';
$db['subdomain2']['dbprefix'] = '';
$db['subdomain2']['pconnect'] = FALSE;
$db['subdomain2']['db_debug'] = TRUE;
$db['subdomain2']['cache_on'] = FALSE;
$db['subdomain2']['cachedir'] = '';
$db['subdomain2']['char_set'] = 'utf8';
$db['subdomain2']['dbcollat'] = 'utf8_general_ci';
$db['subdomain2']['swap_pre'] = '';
$db['subdomain2']['autoinit'] = TRUE;
$db['subdomain2']['stricton'] = FALSE;

Comments

[Desire]

I'm looking for best practice here! Your approach seems wonderful!

[MonkeyZeus]

I'm glad you like it! Let me know if you run into any snags

[Desire]

thanks, its working! would you suggest any other approach to make it more better, secure & efficient.

[MonkeyZeus]

I am not sure what you mean by "more secure". What are looking to protect against?

[MonkeyZeus]

If you want to make it more efficient then you can maintain the approach I've outline but take the PDO() call out of the equation by hard-coding the different settings for sub-domains.