How to load multiple databases in Codeigniter framework

Abhigyan Singh 13th Jul 2020

In some situation, 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 very simple configuration to load multiple database. 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 comment section.

Authored By Abhigyan Singh

He is a continuous blogger and has blogged on different topic. He loves to surf Internet and always trying to get new Idea about new Technology and Innovations and sharing these great information to all the technology lovers.

Also on DiscussDesk