Connect to Multiple Databases in CodeIgnitor

In some CodeIgnitor projects, connecting to multiple databases to read different data types is required. Fortunately, CodeIgnitor provides an easy way to carry this task out.

To use more than one database, you just need to declare a new element under $db multi-dimensional array in config/database.php .

Define the 2nd database connection

This is how the default database connection looks like:

$db['default'] = array(
	'dsn'	=> '',
	'hostname' => 'localhost',
	'username' => 'db_user',
	'password' => 'db_pass',
	'database' => 'db_default',
	'dbdriver' => 'mysqli',
	'dbprefix' => '',
	'pconnect' => FALSE,
	'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
);

Copy and modify those codes to suit the 2nd database’s credential. In my case, database name is changed to “seconddatabase”, hostname, username and password is also changed.

$db['seconddatabase'] = array(
	'dsn'	=> '',
	'hostname' => '192.168.1.2', 
	'username' => 'db_user2',
	'password' => 'db_pass2',
	'database' => 'db_second',
	'dbdriver' => 'mysqli',
	'dbprefix' => '',
	'pconnect' => FALSE,
	'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
);

If you need to have another database connection, the same way is applied.

Using the 2nd database

Now you have 2 databases in the same project. Before building a query, you need to specify which database to use.

//connect to 2nd database and query
$db = $this->load->database('seconddatabase', TRUE);
$query = $db->select('*')->from('article')->where($conditions)->order_by('time', 'DESC')->get();
$result = $query->result();

//connect to default database and query: it is kept the same like when connecting to only 1 database
$query = $this->db->select('*')->from('user')->where($conditions)->order_by('id', 'DESC')->get();
$result = $query->result();

Close database

//2nd database
$db->close();
//default database
$this->db->close();