Connecting multiple database in codeigniter

Sometimes we need to use two or multiple database for a single site. If you need to connect to more than one database simultaneously. codeigniter provide an easy handy feature to connect multiple database. for this we need to add some config of databases and load the db instance. we need to set only PCONNECT(persistent connection) to set TRUE/FALSE. Below we will see how to connect multiple databse in codeigniter with direct loading db object in controller/model and how to connect multiple database in codeigniter using library.

1. set your database configuration :- go to “application/config/database.php” and add below code (add your db settings)

$db['default']['hostname'] = 'localhost';
$db['default']['username'] = 'root';
$db['default']['password'] = '';
$db['default']['database'] = 'test1';
$db['default']['dbdriver'] = 'mysql';
$db['default']['dbprefix'] = '';
$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;
 
//Another database connection.
 
$db['db2']['hostname'] = 'localhost';
$db['db2']['username'] = 'root';
$db['db2']['password'] = '';
$db['db2']['database'] = 'test2';
$db['db2']['dbdriver'] = 'mysql';
$db['db2']['dbprefix'] = '';
$db['db2']['pconnect'] = FALSE;
$db['db2']['db_debug'] = TRUE;
$db['db2']['cache_on'] = FALSE;
$db['db2']['cachedir'] = '';
$db['db2']['char_set'] = 'utf8';
$db['db2']['dbcollat'] = 'utf8_general_ci';
$db['db2']['swap_pre'] = '';
$db['db2']['autoinit'] = TRUE;
$db['db2']['stricton'] = FALSE;

2. Use in controller/model :-

Using direct load db instance :- after set configuration we need to only load and use database objects.

// Running first db query.
$qry = $this->db->query("SELECT * FROM users ORDER BY `u_id` DESC");
print_r($qry->result());
 
// Loading second db and running query.
$CI = &get_instance();
//setting the second parameter to TRUE (Boolean) the function will return the database object.
$this->db2 = $CI->load->database('db2', TRUE);
$qry = $this->db2->query("SELECT * FROM cds");
print_r($qry->result());

Using library :- create a library file in “application/libraries/multipledb.php” (change file name as you want)

<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');
class Multipledb {
  var $db = NULL;
  function __construct(){
    $CI = &get_instance();
    $this->db = $CI->load->database('db2', TRUE); 
  }
  // Add more functions two use commonly.
  public function save(){
  }
}

Use library in controller/model/view :-

$this->load->library('multipledb'); // loading library.
$query2 = $this->multipledb->db->query('your query goes here'); // running query using library.
$this->multipledb->save();// calling library function.

Now you have all set to connecting two or multiple databases in codeigniter.