Connecting to Multiple Databases

Comments

• admin

  December 2010 edited 5:21AM

  To be honest, connecting to a second isn't really "supported" meaning, we haven't done much testing with that, since it's a scenario we haven't run into. Regardless, I'd like to see if we can get it to work for you in case we do run into that situation. Could you email me your controller, model and SQL information to see if I can replicate the issue (info@getfuelcms.com)?
• psc

  December 2010 edited December 2010

  I was able to get it to connect to both DBs properly by changing line 117 of fuel/application/core/MY_model.php to:
  
  $this->db = $this->load->database($this->dsn, true);
  
  originally it is:
  
  $this->load->database($this->dsn);
• psc

  December 2010 edited 5:21AM

  Err. That worked good for a minute.
  
  I am trying to use CI's ability to store session data in the database, and that put me back to square one. The session update query fails (line 288 in fuel/codeigniter/libraries/Session.php)
  
  In that file it is calling the database using $this->CI->db->query. *stumped*
• admin

  December 2010 edited December 2010

  What was the error message you were seeing? Was it a PHP error message or a connection to the database error message?
• psc

  December 2010 edited 5:21AM

  It wasn't a php error. It said that permission was denied for my db user connecting to table ci_session - not sure why it would be denied; unless it is using the wrong dsn connection (using the wrong database, as the dbuser i'm using has permissions on both databases I'm connecting to).
• admin

  December 2010 edited 5:21AM

  I think the issue has to do with the database not being loaded onto the super object.
  
  If the database is loaded with the last parameter is TRUE like this:
  $this->db = $this->load->database($dbparams, TRUE, TRUE);
  
  It will return the database object but won’t attach it to the super global $CI object so the session object doesn’t have access to it.
  
  $this->db = $this->load->database($dbparams, TRUE, FALSE);
  
  Will load it onto the super global object so you can access it like so:
  
  $CI =& get_instance();
  $CI->db->.....
  
  In MY_Model, each model has it’s own db object instead of a reference to the $CI->db. This allows them to act independently. So when loaded by adding the dsn property to the user_model, it get’s loaded to the model but not onto the super object.
  
  I hope that makes sense and helps with your problem.
• admin

  December 2010 edited 5:21AM

  A couple other things, the model's dsn property for the secondary database needs to be set before the parent constructor is run. Also, you may want to set the pconnect property to FALSE for the second database.
• swampthang

  May 2011 edited May 2011

  I had searched for a thread on multiple databases and this was the only one I found. I was able to find a quick and easy solution and thought I'd post it here.
  
  I had a client with 2 divisions of their company with 2 separate websites but they needed to share a retailer finder module I had constructed for the first one. In other words, they wanted to admin in one CMS and have it populate the the retailers data in the other one.
  
  I edited fuel/application/config/database.php by adding the additional database connection info as another array like so:
  $db['default']['hostname'] = "localhost"; $db['default']['username'] = "dbuser1"; $db['default']['password'] = "dbpass1"; $db['default']['database'] = "db1"; $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"; // db for snagging the retailers info $db['secondary']['hostname'] = "localhost"; $db['secondary']['username'] = "dbuser2"; $db['secondary']['password'] = "dbpass2"; $db['secondary']['database'] = "db2"; $db['secondary']['dbdriver'] = "mysql"; $db['secondary']['dbprefix'] = ""; $db['secondary']['pconnect'] = TRUE; $db['secondary']['db_debug'] = TRUE; $db['secondary']['cache_on'] = FALSE; $db['secondary']['cachedir'] = ""; $db['secondary']['char_set'] = "utf8"; $db['secondary']['dbcollat'] = "utf8_general_ci";
  The retailers model looked something like this:
  class Retailers_model extends Base_module_model { function __construct() { parent::__construct('retailers'); // table name } /* need to use a basic query in a function rather than retailers_model->find_one_by_id($id) in the view because we are connecting to the other site's db. (I'm calling this from the view - I'm sure there's a cleaner way to do it but I was in a hurry and feeling lazy) */ function get_retailer($id) { $db = $this->load->database('secondary', TRUE, TRUE); $query = $db->query(" SELECT * FROM retailers WHERE id=$id "); $rows = $query->result_array(); return $rows[0]; } // this is a function to pull in a google map if you're interested function find_retailers($lat, $lng, $dist) { $db = $this->load->database('secondary', TRUE, TRUE); $query = $db->query(" SELECT id, name, address, city, state, zipcode, phone, lat, lng, ( 3959 * acos( cos( radians( $lat ) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians( $lng ) ) + sin( radians( $lat ) ) * sin( radians( lat ) ) ) ) AS distance FROM retailers HAVING distance <$dist ORDER BY distance LIMIT 0 , 10 "); $data = $query->result_array(); return $data; } // more functions here }
  This worked perfectly. The only thing you have to sure about is that both databases can be addressed using 'localhost' or that the IP address can be used for the host variable.
  
  Hope that helps someone.
• atticusservices

  February 2012 edited 5:21AM

  I came up with a simpler solution as it appears the functionality may have been added after this question was originally asked. Essential all you need to do is set the "dsn" value in your model.
  
  I just wrote a quick post about it if anyone needs more information.
  http://labs.ayzenberg.com/2012/02/fuelcms-multi-database/
• marcus_at_localhost

  July 2018

  Just for the record, this was the solution by @atticusservices:

  $db['master_db']['hostname'] = "db01.website.com";
  $db['master_db']['username'] = "root";
  $db['master_db']['password'] = "";
  $db['master_db']['database'] = "database_name";
  $db['master_db']['dbdriver'] = "mysql";
  
  $db['slave_db']['hostname'] = "db02.website.com";
  $db['slave_db']['username'] = "root";
  $db['slave_db']['password'] = "";
  $db['slave_db']['database'] = "database_name";
  $db['slave_db']['dbdriver'] = "mysql";
  
  $active_group = "slave_db";
  class Foo_model extends Base_module_model {
     protected $dsn = 'master_db';
     function __construct(){}
  }
  

  source: https://web.archive.org/web/20130918114327/http://labs.ayzenberg.com/2012/02/fuelcms-multi-database/