How can I switch to another database during runtime?

How can I switch to another database during runtime? - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: How can I switch to another database during runtime? (/showthread.php?tid=32268)

How can I switch to another database during runtime? - El Forum - 07-18-2010

[eluser]Isamtron[/eluser]
Hello,

I'm wondering how it's possible to switch the main database used in my CI app during runtime to execute a few commands on another one then switch back to the original.

Please help. Thanks.

How can I switch to another database during runtime? - El Forum - 07-18-2010

[eluser]KingSkippus[/eluser]
[quote author="Isamtron" date="1279488858"]Hello,

I'm wondering how it's possible to switch the main database used in my CI app during runtime to execute a few commands on another one then switch back to the original.

Please help. Thanks.[/quote]

Look in your configuration file located in system/application/config/database.php. You should have a two-dimenional array defined as:

Code:
$db['default']['hostname'] = "localhost";

$db['default']['username'] = "";

$db['default']['password'] = "";

// ...

All you have to do is add another database below it and call it something besides 'default'. For example:

Code:
// I prefer defining the array this way instead of typing everything

// a zillion times.

$db['secondary'] = array(

  'hostname' => 'localhost',

  'username' => 'db_user',

  'password' => 'my_password',

  'database' => 'foo_data',

  'dbdriver' => 'mysql',

  'dbprefix' => '',

  'pconnect' => true,

  'db_debug' => true,

  'cache_on' => false,

  'cachedir' => '',

  'char_set' => 'utf8',

  'dbcollat' => 'utf8_general_ci' );

Then in your code, when you want to use that database, just do this:

Code:
$dbh = $this->load->database('secondary', true);

$dbh->select('id, name, phone');

$dbh->from('employees');

$dbh->where('id', 1337);

$query = $dbh->get();

foreach ($query->result() as $row) {

  // Do something with the data

}

I will warn you that I've had mixed results in working with another database when trying to go back to using the original default database. Personally, I never use $this->db, I always assign a database handle to the database I'm using by specifying true as the second parameter to $this->load->database() and use the handle instead, so my code pretty much looks like the above all the time.