Multiple Database Connection in CodeIgniter

You Can Connect to Multiple Database in the same Codeigniter application easily. To use Multiple Database Connections you have to crate multiple Configurations Arrays.

By default CI had following line of code to connect to one database.The default group will be loaded automatically during $this->db call.

$db['default']['hostname'] = 'localhost';
$db['default']['username'] = 'root';
$db['default']['password'] = '';
$db['default']['database'] = 'test';
$db['default']['dbdriver'] = 'mysql';
$db['default']['dbprefix'] = '';
$db['default']['pconnect'] = TRUE;
$db['default']['db_debug'] = FALSE;
$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'] = FALSE;
$db['default']['stricton'] = FALSE;

So, for another database connection you have to Create Another Configuration Array as shown below.

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

How to Connect to new Database

For connecting to new database you need to specify the database name along with default connection statement.

  $this->load->database(second, TRUE)

How to use new Database

After loading new database , you can perform DB operations as shown below

// load second database
$this->legacy_db = $this->load->database(database_2, true);
// fetch result from test_table
$this->legacy_db->select ('*');
$this->legacy_db->from ('test_table');
$query = $this->legacy_db->get();
$result = $query->result ();

How to close the Connections

While CodeIgniter intelligently takes care of closing your database connections, you can explicitly close the connection.

   $this->db->close(); // for default Connection
  $this->legacy_db->close(); // for second Connection