Connecting to Multiple Databases

psc · December 2010

Hi. I'm working on a log in form that will connect to a separate database to check against the user's table (so I don't have to require users to sign up twice to interact with my fuelcms-run site).

I've managed to build the form and it works great. I've integrated the log in view into my fuelcms block header.

My question is, if the user fails the log in form, I want to display a regular fuel cms page with the log in error and let them try again.

I've connected to the second database from within my user_model. When i fail the log in, I get an error that the fuel_bocks table can't be found from within the wrong database (it's not there).

So what is the proper protocol for connecting to two databases using Fuel? The error I receive is on line 244 of MY_models.php, but I only want to connect to this secondary database to test the user's log in credentials (which is why I put the database info within my user_model). I set the new database using:
$this->secondDB = $this->load->database($dbInfo, TRUE);

and can make calls using $this->secondDB->query, etc. But I don't want to affect the regular $this->db->query calls.

admin · December 2010

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

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

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

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

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

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

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

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

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/

Howdy, Stranger!

Categories

Connecting to Multiple Databases

Comments