Codeigniter - Where to create the second database connection?

Question

I have to use multiple database connections in my application. The scenario is:

• I have a dm_masterdb that holds the database information and the user credentials to login to the app
• After logging into the app, this dm_masterdb is no more needed, the database information of the logged in user is taken from the database and a connection is made according to his credentials. And now whole application operates on this newly created database connection, say userDb.

Now what I have been doing is:

I have created a the following helper that aids in connecting to the second database:

/**
 * Aids in connecting to the passed database
 * @param  string $db_name database name to which the connection is required
 * @return object          Database object for the connection made
 */
function connectDb($db_name) {
    // Get current Codeigniter instance
    $CI =& get_instance();

    try {

        $userDbConfig['hostname'] = $CI->db->hostname;
        $userDbConfig['username'] = $CI->db->username;
        $userDbConfig['password'] = $CI->db->password;
        $userDbConfig['database'] = $db_name;
        $userDbConfig['dbdriver'] = "mysqli";
        $userDbConfig['dbprefix'] = "";
        $userDbConfig['pconnect'] = FALSE;
        $userDbConfig['db_debug'] = TRUE;
        $userDbConfig['cache_on'] = FALSE;
        $userDbConfig['cachedir'] = "";
        $userDbConfig['char_set'] = "utf8";
        $userDbConfig['dbcollat'] = "utf8_general_ci";

        $userDb = $CI->load->database($userDbConfig, true);

        return $userDb;

    } catch (Exception $e) {

        $error = 'The error thrown is: ' . $e->getMessage();
        $error  .=  'Error thrown while database connection to ' . $db_name;

        show_error($error, 500);
        log_message( 'error', $error );
    }
}

This function connectDb() is called in the constructor of each and every model to create a database connection before accessing the database. For example one of my models is given below:

class Payments extends CI_Model {

    private $userDb;

    public function __construct()
    {
        parent::__construct();
        $this->userDb = connectDb($this->session->userdata('db_name'));
    }

    public function fetchChartData($period, $type)
    {
        //...
        $result = $this->userDb->query($query);
        return $result->result_array();
    }
}

Now the question is,

1. Is this the right way that I am doing it?
2. Is there any way that I can make it more efficient?
3. Would it be possible that I can drop off the existing database connection to dm_masterdb and access this connection to user's database globally i.e. without having to create database connection in each and every model's constructor?

Answer 1

Soon I realized that, all that making two connections did was to slow down my app.

Finally I went with: Not create the second connection but to access the second database using it's dbname.tableName in my queries and the app was more efficient comparatively.

Answer 2

You could extend the CI_Model for this purpose. First create a base model under application/core say MY_Model.php. The prefix MY_ depends on a variable $config['subclass_prefix'] = 'MY_'; in the config file.

MY_Model.php

class MY_Model extends CI_Model{
 protected $myDB =null;

 public function __construct(){
        $this->connect_db();
 } 

 public function connect_db(){
       /***First fetch the configuration here from dm_masterdb 
           and assign it to the following array as needed ***/

        //assign the values
        $userDbConfig['hostname'] ='fetched value from above';
         ...................................
        $userDbConfig['dbcollat'] = "utf8_general_ci";  
        $this->myDB = $this->load->database($userDbConfig, TRUE);
 }

}

Now under your models folder , say you have a model called test_model

test_model.php

class Test_model extends MY_Model
{


    function __construct()
    {
        parent::__construct();
    }
    public function get_result(){
        $t = $this->db->query('query on default db')->result_array();
        $t1 = $this->myDB->query('query on your dynamic db')->result_array();
        echo "<pre>";
        print_r($t1);
        print_r($t);

    }
}

Hope this helps you...

Answer 3

In your database.php you have default configuration like

$db['default']['hostname'] = 'localhost';
$db['default']['username'] = 'root';
$db['default']['password'] = '';
..............
..............

Copy this configuration and change default index to something other then default like

$db['second_db']['hostname'] = 'localhost';
$db['second_db']['username'] = 'root';
$db['second_db']['password'] = '';
..............
..............

to use second db

$secondDb = $this->load->database('second_db', TRUE);

then you will use $secondDb->foo() instead $this->db->foo()

Second way is to manually connect your database like

$config['hostname'] = "localhost";
$config['username'] = "myusername";
$config['password'] = "mypassword";
$config['database'] = "mydatabase";
$config['dbdriver'] = "mysql";
$config['dbprefix'] = "";
$config['pconnect'] = FALSE;
$config['db_debug'] = TRUE;
$config['cache_on'] = FALSE;
$config['cachedir'] = "";
$config['char_set'] = "utf8";
$config['dbcollat'] = "utf8_general_ci";

$this->load->database($config);

For more information see here http://ellislab.com/codeigniter/user-guide/database/connecting.html