How to Connect to Multiple Databases in CodeIgniter

Connecting to multiple databasesis simpler in CodeIgniter. All you have to do is to establish a separate connection for each database you wish to work. Here I'll show you how to connect with two MySQL Databases.

1. Open the "application/config/database.php" file.

2. You can see a list of default connection settings provided. Now enter the hostname, username, password, database & database prefix (if any) you want to connect.

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

Recommended Read: PHP Code to Connect with MySQL DB using MySQL and MySQLi Extensions

3. Make another copy of the connection settings with different name and provide the second database details you want to connect.

$db['ADMINDB']['hostname'] = 'localhost';
$db['ADMINDB']['username'] = 'mysql_username';
$db['ADMINDB']['password'] = 'mysql_password';
$db['ADMINDB']['database'] = 'admin';
$db['ADMINDB']['dbdriver'] = 'mysql';
$db['ADMINDB']['dbprefix'] = 'kms';
$db['ADMINDB']['pconnect'] = TRUE;
$db['ADMINDB']['db_debug'] = TRUE;
$db['ADMINDB']['cache_on'] = FALSE;
$db['ADMINDB']['cachedir'] = '';
$db['ADMINDB']['char_set'] = 'utf8';
$db['ADMINDB']['dbcollat'] = 'utf8_general_ci';
$db['ADMINDB']['swap_pre'] = '';
$db['ADMINDB']['autoinit'] = TRUE;
$db['ADMINDB']['stricton'] = FALSE;

4. Now you can access the databases like this,

//access default database
$this->load->database();
$query = $this->db->get('staff');
foreach ($query->result() as $row)
     echo $row->name;

//access the second database
$admin_db= $this->load->database('ADMINDB', TRUE);
$query = $admin_db->get('members');
foreach ($query->result() as $row)
     echo $row->role;

Note for Newbies: CodeIgniter comes with MySQL Driver by default and if you want to work with other databases like MSSQL Server then you have to install the driver manually and the connection settings will differ accordingly.

You can connect with two or more databases in CodeIgniter in the same way. Follow the same procedure if you want to connect to databases other than MySQL.