Dynamic Database Switch - Codeigniter

Question

I want to switch my codeigniter multiple database on runtime. My Default database will run thoroughly the page but when I needed to switch to other database based on scenario or requirement then I can do. The common model function will work for the all different databases. So, I want to use same model and same function for multiple database connection using dynamic selector without using session or without passing function variable

To achieve this I set a name in cofig and when I call my model I set the required database name in controller before calling model and then I tried to get the name in model which is set in controller. But unfortunately I'm not getting the name from controller to model.

Database Configuration File -

$db['default'] = array(
               'dsn'    => '',
               'hostname' => 'localhost',
               'username' => 'root',
               'password' => 'pass'
               'database' => 'db1'
                .......
               );


$db['anotherDB'] = array(
               'dsn'    => '',
               'hostname' => 'localhost',
               'username' => 'root',
               'password' => 'pass'
               'database' => 'db2'
                .......
               );

Controller -

$this->config->set_item('active_db', 'anotherDB');
$sql = 'select * from user';
$anotherDB_record = $this->model->customQuery($sql);

print_r($anotherDB_record);


$this->config->set_item('active_db', 'default');
$sql = 'select * from customer';
$default_record = $this->model->customQuery($sql);

print_r($default_record);

Mode -

   protected $database;
   function __construct() {
       parent::__construct();
       $oDB = $this->config->item('active_db');
       $this->database = $this->load->database($oDB, TRUE);
   }    
   function customQuery($sql){
       $query = $this->database->query( $sql );
       return $query->result();
   }

This is the way I have tried to switch my database. If you guys have any other best solution to switch multiple database then please feel free to suggest me.

Answer 1

Try bellow example for dynamically configure another database

common model

function getOtherDB($groupID) {
    $getRecord = $this->common_model->getRow('group_master', 'GroupID', $groupID);
    if ($getRecord) {
        $config['database'] = $getRecord->DBName;
        $config['hostname'] = $getRecord->DBHostIP;
        $config['username'] = $getRecord->DBHostUName;
        $config['password'] = $getRecord->DBHostPassw;
        $config['dbdriver'] = "mysqli";
        $config['dbprefix'] = "";
        $config['pconnect'] = FALSE;
        $config['db_debug'] = TRUE;
        $DB2 = $this->load->database($config, TRUE);
        if ($DB2) {
            return $DB2;
        }
    }
    return FALSE;
}

In the above example, I have group_master table which has group wise database details by passing GroupId I fetch the record and set Another database according to group Make sure your all database configuration information stored in the database is encrypted format Use below example to fire query on Other database

$result = $DB2->query("select * from group");
// $DB2 is other database instance you can create multiple db connection using above methos