Codeigniter - multiple database connections

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

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());

Use this.

$dsn1 = 'mysql://user:password@localhost/db1';$this->db1 = $this->load->database($dsn1, true);     $dsn2 = 'mysql://user:password@localhost/db2';$this->db2= $this->load->database($dsn2, true);     $dsn3 = 'mysql://user:password@localhost/db3';$this->db3= $this->load->database($dsn3, true);   

Usage

$this->db1 ->insert('tablename', $insert_array);$this->db2->insert('tablename', $insert_array);$this->db3->insert('tablename', $insert_array);