How to load multiple databases in Codeigniter framework

In some situations, we need to load or connect multiple databases in our application to perform various database operations. Here we will show to connect two databases in codeigniter framework.

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

Step1:  Database Configuration file of Codeigniter

To open Database Configuration file, go to application/config/database.php

First 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' => 'DATABASE_NAME_1',
'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' => 'DATABASE_NAME_2',
'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());

?>

Important Setting: Sometimes Codeigniter cannot connect multiple database in persistent connection. So you 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);
}

?>

Now we have learnt to load multiple databases in Codeigniter framework. If you have any query feel free to ask in the comment section.