CodeIgniter Forums

Full Version: Multiple Databases - Where can I store config data from database for application use?
You're currently viewing a stripped down version of our content. View the full version with proper formatting.

El Forum

[eluser]ladooboy[/eluser]
Hi !

I've got two sets of databases.

Users
Data1

I have to make 1 call at login time to Users db to get the correct DB connection details out of the db and then load the new database to be used for the rest of the session.

Basically this is what I do:

Code:
$CI->udb = $this->load->database($database_details,TRUE);
$this->udb = & $CI->udb;

In MY_MODEL I do this:

Code:
$CI =& get_instance();
    $this->udb = $CI->udb;

I want to make sure it saves the connection of the dynamic db connection in the superglobal, so I can re use it in every model(not sure if that's the right way ?)

My problem is that this works for the current model, but as soon as I try to make the call from a different model the superglobal udb is empty and it can't find the reference to my dynamic DB anymore.



Here are my config settigs
Code:
$db['localhost']['hostname'] = 'localhost';
$db['localhost']['username'] = 'root';
$db['localhost']['password'] = '';
$db['localhost']['database'] = 'user';
$db['localhost']['dbdriver'] = 'mysql';
$db['localhost']['dbprefix'] = '';
$db['localhost']['pconnect'] = FALSE;
$db['localhost']['db_debug'] = TRUE;
$db['localhost']['cache_on'] = FALSE;
$db['localhost']['cachedir'] = '';
$db['localhost']['char_set'] = 'utf8';
$db['localhost']['dbcollat'] = 'utf8_general_ci';
$db['localhost']['swap_pre'] = '';
$db['localhost']['autoinit'] = FALSE;
$db['localhost']['stricton'] = FALSE;

This is my config array from the DB:
Code:
$db['localhost']['hostname'] = 'localhost';
$db['localhost']['username'] = 'root';
$db['localhost']['password'] = '';
$db['localhost']['database'] = 'data1';
$db['localhost']['dbdriver'] = 'mysql';
$db['localhost']['dbprefix'] = '';
$db['localhost']['pconnect'] = TRUE;
$db['localhost']['db_debug'] = TRUE;
$db['localhost']['cache_on'] = FALSE;
$db['localhost']['cachedir'] = '';
$db['localhost']['char_set'] = 'utf8';
$db['localhost']['dbcollat'] = 'utf8_general_ci';
$db['localhost']['swap_pre'] = '';
$db['localhost']['autoinit'] = FALSE;
$db['localhost']['stricton'] = FALSE;

How can I make sure the new DB connection is available throughout my application without having to re-establish a new connection every time (pconnect is set to TRUE for the dynamic one).

Any help is appreciated.

El Forum

El Forum

[eluser]ladooboy[/eluser]
@InsiteFX

Thanks for your reply, but a reference to the user guide doesn't really help me any further.

I have rephrased my post to make sure it's clearer what I want. I can't seem to keep the connection details in the superglobal as it's not available when moving to any other pages.

El Forum

[eluser]Nick Woodhead[/eluser]
Set up your db config file like this

$db['user']['hostname'] = 'localhost';
$db['user']['username'] = 'root';
$db['user']['password'] = '';
$db['user']['database'] = 'user';
$db['user']['dbdriver'] = 'mysql';
$db['user']['dbprefix'] = '';
$db['user']['pconnect'] = FALSE;
$db['user']['db_debug'] = TRUE;
$db['user']['cache_on'] = FALSE;
$db['user']['cachedir'] = '';
$db['user']['char_set'] = 'utf8';
$db['user']['dbcollat'] = 'utf8_general_ci';
$db['user']['swap_pre'] = '';
$db['user']['autoinit'] = FALSE;
$db['user']['stricton'] = FALSE;

$db['default']['hostname'] = 'localhost';
$db['default']['username'] = 'root';
$db['default']['password'] = '';
$db['default']['database'] = 'data1';
$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['default']['swap_pre'] = '';
$db['default']['autoinit'] = FALSE;
$db['default']['stricton'] = FALSE;

I autoload the 'database' library which loads the default database which will be used throughout your application. In your user model you can then set it up like this to be able to access both DB's

class User_model extends CI_Model {
private $db_user;

public function __construct() {
$this->db_user = $this->load->database('user', TRUE);
}

public function get_info() {
$user_query = $this->db_user->query($user_sql); //get info from user DB
$query = $this->db->query($sql); //get info from default DB - can be called in any model
}
}


Hope this helps!

El Forum

[eluser]Nick Woodhead[/eluser]
Sorry after re-reading your post and my reply I realized it probably didn't really help you so heres a second attempt lol.

Create a model and autoload it in config/autoload.php :

class dynamic_db extends CI_Model {

public $db;

function __construct()
{
parent::__construct();
}

public function load_db($database_details) {
$this->db = $this->load->database($database_details, TRUE);
}
}


In your user model get the dynamic config settings:


class User_model extends CI_Model {
private $db_user;

public function __construct() {
$this->db_user = $this->load->database(‘user’, TRUE);
}

public function login() {
//validate your login and get dynamic db credentials
$user_query = $this->db_user->query($user_sql); //get info from user DB

$this->dynamic_db->load_db($database_details);
}
}

you should then be able to use this in any model for the dynamic db:

$this->dynamic_db->db->query($sql);

El Forum

[eluser]ladooboy[/eluser]
@Nick
Thanks for the answer. I will give it a go today. I was just wondering if there is another way to be able to just use $this->db2.

El Forum

[eluser]Nick Woodhead[/eluser]
You could probably do it two ways...

1) Instead of creating the dynamic_db model create MY_model extends CI_Model with the same functionality and then have your other models extend MY_Model instead of CI_Model

2) If you don't need access to the user db after the login process then you could just autoload the user database as the default in config/autoload.php and then overwrite the $this->db during the login process once you have retrieved the new db credentials

function login () {
$this->db->query('user db query'); //validate login and get db credentials
$this->db = $this->load->database($database_details, TRUE);
$this->db->query('dynamic db query'); //should be available in all models now
}

El Forum

[eluser]ladooboy[/eluser]
@Nick Thanks for the help, it's working now.

I've already had MY_Model so I went again with that approach. I had tried it before, but it wouldn't work. Just to summarize what I did, in case someone else is having the problem.

All my models are extending MY_Model. I am loading this method from MY_Model constructor.

Code:
public function load_user_db()
  {
   $db_name = $this->session->userdata('db_name');
   if ($db_name AND $db_name != '')
   {
     $config = $this->config->item('database_details');
     $config['database'] = $db_name;
     $this->udb = $this->load->database($config,TRUE);

     return TRUE;
   }
    return FALSE;
  }

When I login in my auth_model I do this:

Code:
$database_details = (array)json_decode($user_result->database_details);
  
$this->udb = $this->load->database($database_details,TRUE);    
$query =        $this->udb->get($this->tables['users']);

This is just so that my auth_model has access to the new database as well.

Few things I've came across is:

1. I couldn't make it use the session table in the data1 Databases(because name could be anything), but only in the default session table. This means I also needed to leave these properties for the users database as TRUE, otherwise it would slow down the app and add additional 2 seconds to load the pages.
Code:
$db['localhost']['pconnect'] = TRUE;
$db['localhost']['autoinit'] = TRUE;

2. I had to alter the DB_driver.php to add "$this->db_select();" select db