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,

Is this the right way that I am doing it?
Is there any way that I can make it more efficient?
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?

ellislab.com/codeigniter/user-guide/database/connecting.html — Nouphal.M
– Nouphal.M, Commented Dec 29, 2013 at 10:23
@Nouphal.M thanks for your reply. But I've already gone throught the documentation but they haven't mentioned anything regarding the placement of code (for the dynamic connection) etc — Kamran Ahmed
– Kamran Ahmed, Commented Dec 29, 2013 at 10:33
You could extend the CI_Model and create your own parent model and move your code there and then extend your models from it.. — Nouphal.M
– Nouphal.M, Commented Dec 29, 2013 at 10:58

Bilal · Accepted Answer · 2013-12-29 12:49:28Z

1

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

answered Dec 29, 2013 at 12:49

Bilal

2,6733 gold badges29 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Kamran Ahmed Over a year ago

bhai thanks for your answer :-) But the problem is, the second database's connection information is being fetched dynamically from the database dm_masterdb and there is no way that I can specify that in database.php prehand.

Bilal Over a year ago

Load dm masterdb, then you have your information and load second database.

Nouphal.M · Accepted Answer · 2013-12-29 16:21:35Z

1

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...

edited Dec 29, 2013 at 16:21

answered Dec 29, 2013 at 10:45

Nouphal.M

6,3281 gold badge20 silver badges28 bronze badges

1 Comment

Kamran Ahmed Over a year ago

If you haven't noticed, I have specified in my question that the information for the second database is being dynamically fetched from the dm_masterdb so there is no way that I can specify that in database.php

Kamran Ahmed · Accepted Answer · 2014-01-07 06:33:03Z

0

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.

answered Jan 7, 2014 at 6:33

Kamran Ahmed

12.5k24 gold badges73 silver badges103 bronze badges

Collectives™ on Stack Overflow

Codeigniter - Where to create the second database connection?

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related