EllisLab text mark
Advanced Search
     
connect to multiple databases
Posted: 16 February 2010 05:27 AM
Joined: 2010-02-16
2 posts

hi,

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

thanks for help

 
Posted: 16 February 2010 05:38 AM   [ # 1 ]   [ Rating: 0 ]
Avatar
Joined: 2007-05-24
717 posts

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.

 Signature 

The art of managing is to explain tomorrow why yesterdays solution doesn’t work today.
livecodes.eu
gwwbouw.nl

 
Posted: 16 February 2010 05:40 AM   [ # 2 ]   [ Rating: 0 ]
Avatar
Joined: 2009-06-12
428 posts

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:

/* 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:

$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).

 Signature 

- Simon

 
Posted: 16 February 2010 06:12 AM   [ # 3 ]   [ Rating: 0 ]
Joined: 2010-02-16
2 posts

THANKS IT WORK…

 
Posted: 24 February 2010 10:47 PM   [ # 4 ]   [ Rating: 0 ]
Joined: 2010-02-24
1 posts

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.

 
Posted: 04 November 2011 04:56 PM   [ # 5 ]   [ Rating: 0 ]
Avatar
Joined: 2010-07-01
5 posts

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.

$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.

 
Posted: 08 November 2011 10:22 AM   [ # 6 ]   [ Rating: 0 ]
Joined: 2011-11-08
3 posts
sarcastron - 04 November 2011 04:56 PM
$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.

This should be in the user guide.

I was stuck with this big time! Thank you a lot. Now I can continue my work.

 
Posted: 17 April 2012 09:00 AM   [ # 7 ]   [ Rating: 0 ]
Avatar
Joined: 2011-12-27
3 posts
sarcastron - 04 November 2011 04:56 PM

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.

$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.


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

 
Posted: 10 July 2012 11:50 AM   [ # 8 ]   [ Rating: 0 ]
Joined: 2012-07-10
1 posts

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:

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 tongue wink

 
Posted: 02 August 2012 06:35 AM   [ # 9 ]   [ Rating: 0 ]
Joined: 2012-08-01
4 posts
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.

 

 

 

 

 
Posted: 15 August 2012 10:45 AM   [ # 10 ]   [ Rating: 0 ]
Avatar
Joined: 2011-04-13
256 posts

did you $this->load->library(‘database’); ?

 Signature 

Expert Web Development Backed by LIFETIME Guarantee
CI-Foundation v1
InfinityTracker - Project Management

 
Posted: 19 December 2013 01:30 AM   [ # 11 ]   [ Rating: 0 ]
Joined: 2013-12-19
1 posts

@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

 
Posted: 03 July 2014 09:38 AM   [ # 12 ]   [ Rating: 0 ]
Joined: 2014-06-10
6 posts

How do you do a join across 2 databases?

In other languages (let’s say perl) I log into one database with an ID that has access to both databases.  I then can access both databases by fully qualifying everything.  I can then do a join across the 2 databases.  How can I do this in codeigniter?
Thanks.