Codeigniter MVC Framework PHP

How to load or connect multiple databases in Codeigniter framework

In some time, we need to connect one or multiple databases in our application or project to perform various database operations.

Codeigniter framework provides very simple configuration to connect multiple databases. The step-by-step instruction is given below to connect multiple databases Codeigniter framework.

Step1: Database Configuration file of Codeigniter

Open Database Configuration file from below path,

go to application/config/database.php

You need to change ‘pconnect’ => FALSE,

Step 2: Make two database connectivity settings

<?php

/* ======= First database connectivity Settings =========*/

$db[‘default’] = array(

‘dsn’ => ”,

‘hostname’ => ‘localhost’,

‘username’ => ‘root’,

‘password’ => ”,

‘database’ => ‘1_DATABASE_NAME’,

‘dbdriver’ => ‘mysqli’,

‘dbprefix’ => ”,

‘pconnect’ => TRUE,

‘db_debug’ => (ENVIRONMENT !== ‘production’),

‘cache_on’ => FALSE,

‘cachedir’ => ”,

‘char_set’ => ‘utf8’,

‘dbcollat’ => ‘utf8_general_ci’,

‘swap_pre’ => ”,

‘encrypt’ => FALSE,

‘compress’ => FALSE,

‘stricton’ => FALSE,

‘failover’ => array(),

‘save_queries’ => TRUE

);

 

/* ======= Second database connectivity Settings =========*/

$db[‘database2’] = array(

‘dsn’ => ”,

‘hostname’ => ‘localhost’,

‘username’ => ‘root’,

‘password’ => ”,

‘database’ => ‘2_DATABASE_NAME’,

‘dbdriver’ => ‘mysqli’,

‘dbprefix’ => ”,

‘pconnect’ => TRUE,

‘db_debug’ => (ENVIRONMENT !== ‘production’),

‘cache_on’ => FALSE,

‘cachedir’ => ”,

‘char_set’ => ‘utf8’,

‘dbcollat’ => ‘utf8_general_ci’,

‘swap_pre’ => ”,

‘encrypt’ => FALSE,

‘compress’ => FALSE,

‘stricton’ => FALSE,

‘failover’ => array(),

‘save_queries’ => TRUE

);

 

?>

 

Step 3: Load multiple database instances in Controller or Model

<?php

// To retrieve data from first database.

$query = $this->db->query(“SELECT * FROM employee order by id desc”);

print_r($query->result());

// To Load second database and perform query operation.

$CI = &get_instance();

$this->db2 = $CI->load->database(‘database2’, TRUE);

$query = $this->db2->query(“SELECT * FROM salary order by emp_id desc”);

print_r($query->result());

?>

 

Note : Sometimes Codeigniter cannot connect multiple database in persistent connection. So we need to put a simple setting in system/database/ DB_driver.php line number 781.

<?php

public function simple_query($sql){

if ( ! $this->conn_id){

if ( ! $this->initialize()){

return FALSE;

}

}

$this->db_select();

//Just Add this line here

return $this->_execute($sql);

}

?>

 

One thought on “How to load or connect multiple databases in Codeigniter framework

Leave a Reply

Your email address will not be published. Required fields are marked *