Using Multiple Databases

[eluser]Kolombo[/eluser]
I am looking to connect to multiple external databases (currently 2, expanding to 6) and am looking for the best/most efficient way of doing this without checking and switching databases within each function.

Currently for example this function will work but it means calling the function twice in the controller and performing this database load within every function that requires it.

Code:

function get_some_information($site){
  $DB = $this->load->database($site, TRUE);  
}

Any ideas on what the most efficient/scalable way of doing this is?

Thanks

[eluser]PhilTem[/eluser]
I guess, this could be a solution to your problem

Code:

function get_some_information($site)
{
  static $DB;
  
  isset($DB) OR $DB = $this->load->database($site, TRUE);
  
  // continue with other code, if necessary.
}

Will load the database only once since it's a static variable that get's instantiated on the first call of the function/method "get_some_information".

[eluser]Kolombo[/eluser]
I think I need to explain this a bit better; I have two external databases to connect to (the structures are the exact same so the SQL queries will not change - just the database)

In the controller I use:

Code:

$content['site1_sales'] = $this->Order_Model->get_sales('site1');
    $content['site1_customers'] = $this->Order_Model->get_customers('site1');  
    $content['site1_products'] = $this->Order_Model->get_products('site1');

    $content['site2_sales'] = $this->Order_Model->get_sales('site2');
    $content['site2_customers'] = $this->Order_Model->get_customers('site2');  
    $content['site2_products'] = $this->Order_Model->get_products('site2');

In the model the corresponding functions look something like this:

Code:

function get_sales($site){
  
    $DB = $this->load->database($site, TRUE);
    
    $todays_timestamp = $this->make_timestamp('day');
    
    $query = $DB->get_where('table', array('timestamp >' => $todays_timestamp));
    
    $sales = $query->num_rows();
    
    return $sales;
  }  

  function get_customers($site){
  
    $DB = $this->load->database($site, TRUE);
    
    $todays_timestamp = $this->make_timestamp('day');
    
    $query = $DB->get_where('table', array('timestamp >' => $todays_timestamp));
    
    $sales = $query->num_rows();
    
    return $sales;
  }  

  function get_products($site){
  
    $DB = $this->load->database($site, TRUE);
    
    $todays_timestamp = $this->make_timestamp('day');
    
    $query = $DB->get_where('table', array('timestamp >' => $todays_timestamp));
    
    $sales = $query->num_rows();
    
    return $sales;
  }

As you can see this means that when you think of the amount of times that

Code:

$DB = $this->load->database($site, TRUE);

is going to be used and the amount of times I would need to post the $site to every single function throughout the project as well as changing the database connection to the relevant site within each and every function and passing the $site variable.

Is there a way that it can be done in a more efficient way?

[eluser]PhilTem[/eluser]
First idea was this:
You could do something like this for every method

Code:

function get_products($site) {

$DB = $this->_load_database($site);

$todays_timestamp = $this->make_timestamp('day');

// continue with regular code
}

and then add this method

Code:

function _load_database($site) {
  static $databases;
  
  isset($databases) OR $databases = array();
  
  isset($databases[$site]) OR $databases[$site] = $this->load->database($site, TRUE);
  
  return $databases[$site];
}

Second idea is this: https://gist.github.com/4594907 which can be used like this in your controller

Code:

$content['site1_sales'] = $this->Order_Model->set_site('site'1)->get_sales();
$content['site1_customers'] = $this->Order_Model->get_customers();  
$content['site1_products'] = $this->Order_Model->get_products();

$content['site2_sales'] = $this->Order_Model->set_site('site2')->get_sales();
$content['site2_customers'] = $this->Order_Model->get_customers();  
$content['site2_products'] = $this->Order_Model->get_products();

and you can switch back and forth between the databases easily even with method chaining

This looks more like the answer to your question (hopefully )

[eluser]Kolombo[/eluser]
Seems to do the trick, thanks!