Codeigniter switch between databases

Question

Im working on an Codeigniter framework and I need to switch between different databases to run queries.

According to Codeigniter 3.0.6 docs, I can use $this->db->db_select('db_name') to dynamically change the DB. But it does not seem to work at all.

I have created a sandbox like this:

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

    br('========Start========');

    $dbs = $this->dbutil->list_databases();

    foreach ($dbs as $db)
    {
        if ($db == 'information_schema')
            continue;

        br($db);

        $this->db->db_select($db);

        if ($this->db->table_exists('users'))
            br('Yes');
        else
            br('No');

        echo $this->db->last_query();

        //$tables = $this->db->list_tables();
        //pp($tables);

        br('-----------------------');
    }

    br('========End========');

The result is: it prints different DB names, but the YES/NO and last_query are all the same, and it always run on the first DB.

So I create another test to manually switch DB and the result is the same.

I also try to remove the DB name in config/database.php and set $this->db->db_select('my_third_db_name') and it always run the query on this third DB.

Did I miss something in the code? or there is bug here?

Thanks

P/S: Im connecting to 1 host only, and there are many databases in this host. And the connection is work fine

Answer 1

What I do:

config/database.php

$db['default']['hostname'] = 'localhost';
$db['default']['username'] = 'user';
$db['default']['password'] = 'pass';
$db['default']['database'] = 'database1';
$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'] = TRUE;
$db['default']['stricton'] = FALSE;

$db['database2']['hostname'] = "localhost";
$db['database2']['username'] = "user";
$db['database2']['password'] = "pass";
$db['database2']['database'] = "database2";
$db['database2']['dbdriver'] = "mysql";
$db['database2']['dbprefix'] = "";
$db['database2']['pconnect'] = TRUE;
$db['database2']['db_debug'] = TRUE;
$db['database2']['cache_on'] = FALSE;
$db['database2']['cachedir'] = "";
$db['database2']['char_set'] = "utf8";
$db['database2']['dbcollat'] = "utf8_general_ci";   

Controller

$this->load->model('second_model');
$this->second_model->insert("tablename", array("id"=>$value_id, "foo"=>$bar));

second_model.php

function __construct() {
    parent::__construct();
    //$this->output->enable_profiler(TRUE);
    $this->db_2= $this->load->database('database2', TRUE);
}
function insert($table, $data)
{
    $this->db_2->insert($table, $data);
    if ($this->db_2->affected_rows() == '1')    return TRUE;
    else                                    return FALSE;
}
//More functions

The problem in this case is that you need a file for each database... but maybe you can modify... something like:

function __construct() {
    parent::__construct();
}
function insert($table, $data, $db="default")
{
    $this->$db = $this->load->database($db, TRUE);
    $this->$db->insert($table, $data);
    if ($this->$db->affected_rows() == '1')     return TRUE;
    else                                    return FALSE;
}
/all CRUD functions you need

And then, when you try to access to a specific database, you only need to send the index name of the database.php file.

I hope it can help you.