connect to multiple databases

[eluser]Unknown[/eluser]
hi,

I need to connect to multiple databases and does anyone know how I should edit database.php why?

thanks for help

[eluser]rogierb[/eluser]
Hi, use the search on this forum and take a look at the userguide.
This has been talked about a lot, the userguide has good explanation of how to connect to multiple database.

[eluser]Maglok[/eluser]
Yes I have tackled this before myself. You have to define a second set of database parameters. CI isn't developed to really have two DB connections though, it is more for swapping test and production dbases. That said there are some tricks around it. So first define a second set of DB info like so:

Code:

/* FORUM */
$active_group = "forum";
$active_record = TRUE;

$db['forum']['hostname'] = "xxxxx";
$db['forum']['username'] = "xxxxx";
$db['forum']['password'] = "xxxxx";
$db['forum']['database'] = "xxxxx";
$db['forum']['dbdriver'] = "mysql";
$db['forum']['dbprefix'] = "";
$db['forum']['pconnect'] = TRUE;
$db['forum']['db_debug'] = TRUE;
$db['forum']['cache_on'] = FALSE;
$db['forum']['cachedir'] = "";
$db['forum']['char_set'] = "utf8";
$db['forum']['dbcollat'] = "utf8_general_ci";

/* TEST SITE */
$active_group = "default";
$active_record = TRUE;

$db['default']['hostname'] = "xxxxx";
$db['default']['username'] = "xxxxx";
$db['default']['password'] = "xxxxx";
$db['default']['database'] = "xxxxx";
$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";

Your active db will be the one you defined LAST.

Once you have done this you can manually connect to the second one (or put it in MY_Controller if you always need to). You can then load your second database like so:

Code:

$this->db_forum = $this->CI->load->database('forum', TRUE);

Access dbase 1 with $this->db and dbase 2 with $this->db_forum (or whatever you called it).

[eluser]Unknown[/eluser]
THANKS IT WORK...

[eluser]Unknown[/eluser]
Only in case someone else runs across this thread as I did.

It is not that the last database information entered is the one loaded. Whatever connection is set to $active_group is the one that is automatically loaded. Hence when it is repeated the latest is the one stored.

$active_group and $active_record only need to be set once, ideally at the top. This lets you change the default database on the fly by changing one variable.

[eluser]Unknown[/eluser]
[quote author="sarcastron" date="1320440204"]This works great, but I ran into issues if I needed to use the two databases in the same controller.

If you need to use the two databases in the same controller you will want to set the persistent connection to false or you might get unexpected results depending on the order in which you use your models.

Code:

$db['other_db']['pconnect'] = FALSE;

I did this for the second array as well, though I didn't test to see if it was necessary.[/quote]


You should be calling the database from a model then call the models from the controller

[eluser]Unknown[/eluser]
This works, but if you are using two databases in the same model (or the same controller, in different models) you will need to call 'db_select' before call any Active Record or Query because maybe other model has initialized another database after you.

In other words, CI automatically selects the last initialize database and do not select it before any action, so if you are using two database in the same request be sure to add 'db_select' before do any action with database in all models.

CI is not prepare for this case.

Sample code in one Model:

Code:

class MyForumModel extends CI_Model {

   private $_DB_FORUM = null;

   /**
    * Construct
    *
    * @return void
    */
   function __construct() {
      parent::__construct();

      $this->_DB_FORUM = $this->load->database('forum', TRUE);
   }

   function sample_function($id, $whatever) {
      // IMPORTANT : Select DB first because we don't know if another Model
      // have initialize another DB after forum DB initialize
      $this->_DB_FORUM->db_select();

      $this->_DB_FORUM->set('createdate', date("Y-m-d H:i:s"));
      (...)
      $this->_DB_FORUM->insert('sometable');
      (...)

   }

(...)
}

Alternative: Reading this StackOverflow Question, you can set 'pconnect' to FALSE (each action will do a new connection to DB, so this workaround is not very good for performance) or hack CI code (Remember to set charset encoding and collation too if charset or collation are different between databases!)

Suggestion for CI Developers:
Add a configuration parameter 'multiple_databases = TRUE' in database.php configuration file in order to add the hacking to code to DB_driver.php. I did it in my code ;-P

[eluser]lucas123[/eluser]

Code:

class Mod_plan extends CI_Model {
private $default;
private $mysql;

function _Mod_plan() {
  parent::_Mod_plan();
  $this->mysql = $this->load->database('mysql',TRUE);
}
function get_plan() {
  $sql = 'SELECT *  FROM plan';
  return $this->mysql->query($sql);
}

I do that but I have this error:

Fatal error: Call to a member function query() on a non-object in ...

Please help me.

[eluser]Mirge[/eluser]
did you $this->load->library('database'); ?

[eluser]Unknown[/eluser]
@Lucas163

class Mod_plan extends CI_Model {
private $default;
private $mysql;

function _Mod_plan() {
parent::_Mod_plan();
$this->mysql = $this->load->database('mysql',TRUE);
}
function get_plan() {
$this->mysql->db_select();
$sql = 'SELECT * FROM plan';
return $this->mysql->query($sql);
}


please look it again it would help you, u need to put " $this->mysql->db_select();" inside you function