Connecting to multiple databases is simpler in CodeIgniter. All you have to do is to establish a separate connection for each database you wish to work. At times you may want to work with multiple databases from same or different servers. In core PHP, you can do it by creating separate connection object. And in codeigniter you have to set up separate connection settings for each database you wish to connect. Here I'll show you how to connect with two MySQL Databases.
Connecting to Mulitiple Databases in CodeIgniter
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.
Read Also: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.
