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

Are the same credentials (userid/password) valid on both databases?? — RiggsFolly
– RiggsFolly, Commented May 11, 2016 at 22:27
@RiggsFolly Im connecting to 1 host only and it works normally. I just want to switch the DB, not the host — Louis L
– Louis L, Commented May 11, 2016 at 22:32
@elddenmedio I just want to switch the DB only, not the host — Louis L
– Louis L, Commented May 11, 2016 at 22:33
Yes, but I can have 2 databases on the same host and the same account will not be able (and probably should not be able) to connect to both databases. MYSQL Security is far more granular than you seem to realise. Unless you are using root and that would be ridiculous in a real environment — RiggsFolly
– RiggsFolly, Commented May 11, 2016 at 22:37

Eloy Fernández Franco · Accepted Answer · 2016-05-11 23:03:01Z

4

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.

edited May 11, 2016 at 23:03

answered May 11, 2016 at 22:48

Eloy Fernández Franco

1,3822 gold badges25 silver badges48 bronze badges

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

3 Comments

Louis L Over a year ago

Thanks, cuz my situation is that I have to run a report and the data is came from 100+ dbs in 1 host, so I cannot hardcode 100+ db names like this :D

Eloy Fernández Franco Over a year ago

But the last code can solve your problem.... function insert($table, $data, $db="default"); function getData($table, $fields, $where, $db="default"); etc... all function must have the $db...

Louis L Over a year ago

Yup thanks for that. However, I still wonder why db_select() does not work here. So in short, what is the actual usage of that func?

Collectives™ on Stack Overflow

Codeigniter switch between databases

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related