CodeIgniter with Multiple Database Support

Over the last few months I have been using the CodeIgniter framework for several projects. So far, I have been very pleased with its flexibility and even more so with its extensibility.
On one my projects, I needed to connect to multiple databases at the same time. CI easily supports this via configuration settings, however the built in profiler only supports the default database. No problem, let’s extend it!

Step 1: Edit database.php and use descriptive group names.

In this tutorial, I am connecting to 2 separate databases. So far, this is nothing new, however your settings need to be correct.
Example:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30

$active_group = "default"; $active_record = TRUE;   $db['default']['hostname'] = ""; $db['default']['username'] = ""; $db['default']['password'] = ""; $db['default']['database'] = ""; $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";   // add alternate database settings by gotphp.com   $db['alternate']['hostname'] = ""; $db['alternate']['username'] = ""; $db['alternate']['password'] = ""; $db['alternate']['database'] = ""; $db['alternate']['dbdriver'] = "mysql"; $db['alternate']['dbprefix'] = ""; $db['alternate']['pconnect'] = TRUE; $db['alternate']['db_debug'] = TRUE; $db['alternate']['cache_on'] = FALSE; $db['alternate']['cachedir'] = ""; $db['alternate']['char_set'] = "utf8"; $db['alternate']['dbcollat'] = "utf8_general_ci";

Step 2: Edit autoload.php and autoload ALL models.

This is needed so the profiler is aware of the other databases and can iterate accordingly.
IMPORTANT: If you ONLY load your models on demand, that’s OK too. You still want to follow this step, but simply comment out the $autoload variable when you are NOT in debug mode.
Example:

1 2 3 4

$autoload['model'] = array(     'alternate_model',  // loaded by gotphp.com     'default_model',    // loaded by gotphp.com );

Step 3: In your models, use the actual database group names for your db connection by defining $db_group_name.

In other words, stop using $this->db and start using $this->[group], etc.
IMPORTANT: Be sure to establish a connection to the database group in the model’s constructor AND add a new method to get the database group for this mode. THIS IS REQUIRED.
Example:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31

class Default_model extends Model {       /**      */     var $db_group_name = "default";       // --------------------------------------------------------------------       /**      *  Constructor -- Loads parent class      */     function __construct()     {         parent::__construct();           $this->{$this->db_group_name} = $this->load->database($this->db_group_name, TRUE);       }       // --------------------------------------------------------------------       /**      *  Required method to get the database group for THIS model      */     function get_database_group() {         return $this->db_group_name;     }       // --------------------------------------------------------------------   }

Now in EACH method in THIS model, use $this->{$this->db_group_name}.
Example:

1 2 3 4 5 6 7 8 9 10 11 12 13

class Default_model extends Model {       // --------------------------------------------------------------------       function get_example_data()     {         $query = $this->{$this->db_group_name}->get('default_example');         return $query->result_array();     }       // --------------------------------------------------------------------   }

Step 4: Extend CI’s profiler class to include all databases in the debug output

In your config.php file, take note of your subclass_prefix setting. You will need to use this in order to extend CI’s core classes automatically.
Example:

1	$config['subclass_prefix'] = 'MY_';

Now, create a new file in your application’s libraries directory called MY_Profiler.php.
IMPORTANT: If you changed your subclass_prefix, replace “MY_” in the rest of this tutorial with your custom setting.
In this new file, you will be extending the CI_Profiler, defining your own run method to account for multiple database groups, and adding a new display method that shows the database group AND model for each query!
Example:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67

class MY_Profiler extends CI_Profiler {       /**      * Compile Multiple Database Queries      * @return  string      */     function _compile_multi_db_queries($database, $model)     {         ... too much to copy here ... download file to review ...     }       // --------------------------------------------------------------------       /**      * Run the Profiler      *      * @access  private      * @return  string      */     function run()     {         $output = " <div id="codeigniter_profiler" style="clear: both; background-color: rgb(255, 255, 255); padding: 10px;">";           if ($this->CI->config->item('show_uri_string')) {      $output .= $this->_compile_uri_string(); }         if ($this->CI->config->item('show_controller_info')) {     $output .= $this->_compile_controller_info(); }         if ($this->CI->config->item('show_memory_usage')) {    $output .= $this->_compile_memory_usage(); }         if ($this->CI->config->item('show_benchmarks')) {      $output .= $this->_compile_benchmarks(); }         if ($this->CI->config->item('show_cookies')) {             $output .= $this->_compile_variables('cookie_vars'); }         if ($this->CI->config->item('show_get_vars')) {        $output .= $this->_compile_variables('get_vars'); }         if ($this->CI->config->item('show_post_vars')) {       $output .= $this->_compile_variables('post_vars'); }         if ($this->CI->config->item('show_uri_vars')) {        $output .= $this->_compile_variables('uri_vars'); }         if ($this->CI->config->item('show_tpl_vars')) {        $output .= $this->_compile_variables('tpl_vars'); }         if ($this->CI->config->item('show_session_userdata')) { $output .= $this->_compile_variables('session_userdata'); }           if ($this->CI->config->item('show_db_multi_queries')) {               // Include the autoload config to access the array of models in this app.             include(APPPATH.'config/autoload'.EXT);               // Loop through each model to set the database object             foreach($autoload['model'] as $model) {                   // Define the database object name                 $database = $this->CI->$model->get_database_group();                   // Compile the output                 $output .= $this->_compile_multi_db_queries($database, $model);               }           } else {               $output .= $this->_compile_queries();                  }           $output .= '</div>   ';           return $output;     }       // --------------------------------------------------------------------   }

Step 5: You will need to enable the built in profiler in one of your controllers and set the new configuration paramaters in a profile.php config file.

Example (enable profiler):

1 2 3 4 5

// Load the profile.php config file if it exists $this->config->load('profiler', false, true); if ($this->config->config['enable_profiler']) {     $this->output->enable_profiler(TRUE); }

Example (profiler.php file):

1 2 3 4 5 6 7 8 9 10 11 12

$config['enable_profiler']          = 1; $config['show_uri_string']          = 1; $config['show_controller_info']     = 1; $config['show_memory_usage']        = 1; $config['show_benchmarks']          = 1; $config['show_cookies']             = 1; $config['show_get_vars']            = 1; $config['show_post_vars']           = 1; $config['show_uri_vars']            = 1; $config['show_tpl_vars']            = 1; $config['show_session_userdata']    = 1; $config['show_db_multi_queries']    = 1; // Only enable if you need to show more than one database in the profiler

If you have followed the steps above in order, your CodeIgniter application can now connect to multiple databases at the same time and each connections’ queries will display in the profiler like this: