Codeigniter - multiple database connections

Question

I have to retrieve a MySQL database information from master database and then connect to that database, and fetch some records.

I mean that holding one database I want to load another database.

Is it possible with Codeigniter? Right now I'm using following lines of code in my model.

function connectDb($credential) {      $config['hostname'] = $credential['server'];     $config['username'] = $credential['username'];     $config['password'] = $credential['password'];     $config['database'] = $credential['database'];     $config['dbdriver'] = "mysql";     $config['dbprefix'] = "";     $config['pconnect'] = FALSE;     $config['db_debug'] = TRUE;     $config['cache_on'] = FALSE;     $config['cachedir'] = "";     $config['char_set'] = "utf8";     $config['dbcollat'] = "utf8_general_ci";      $DB2=$this->load->database($config);      $DB2->db->select('first_name,last_name');     $query = $DB2->db->get('person');     print_r($query);  } 

its not working is there any other way?

Answer 1

You should provide the second database information in `application/config/database.php´

Normally, you would set the default database group, like so:

$db['default']['hostname'] = "localhost"; $db['default']['username'] = "root"; $db['default']['password'] = ""; $db['default']['database'] = "database_name"; $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'] = TRUE; $db['default']['stricton'] = FALSE; 

Notice that the login information and settings are provided in the array named $db['default'].

You can then add another database in a new array - let's call it 'otherdb'.

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

Now, to actually use the second database, you have to send the connection to another variabel that you can use in your model:

function my_model_method() {   $otherdb = $this->load->database('otherdb', TRUE); // the TRUE paramater tells CI that you'd like to return the database object.    $query = $otherdb->select('first_name, last_name')->get('person');   var_dump($query); } 

That should do it. The documentation for connecting to multiple databases can be found here: http://codeigniter.com/user_guide/database/connecting.html

Answer 2

The best way is to use different database groups. If you want to keep using the master database as usual ($this->db) just turn off persistent connexion configuration option to your secondary database(s). Only master database should work with persistent connexion :

Master database

$db['default']['hostname'] = "localhost"; $db['default']['username'] = "root"; $db['default']['password'] = ""; $db['default']['database'] = "database_name"; $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'] = TRUE; $db['default']['stricton'] = FALSE; 

Secondary database (notice pconnect is set to false)

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

Then you can use secondary databases as database objects while using master database as usual :

// use master dataabse $users = $this->db->get('users');  // connect to secondary database $otherdb = $this->load->database('otherdb', TRUE); $stuff = $otherdb->get('struff'); $otherdb->insert_batch('users', $users->result_array());  // keep using master database as usual, for example insert stuff from other database $this->db->insert_batch('stuff', $stuff->result_array());