Connecting two database in codeigniter not working

Question

I am trying to connect two database in Codeigniter. I came accross this link Codeigniter - multiple database connections and setup the database as per my requirements. Now the problem is that only the default database is active. Here's the code

Model

function __construct()
    {
        parent::__construct();
        $this->db_2 = $this->load->database('database2', TRUE);
        $this->load->database();

    }

function get_details()
    { 
        $result1 = $this->db->query("select columnname1 from tablename")->result();
        $result2 = $this->db_2->query("select columnname2 from tablename")->result();
    }

I am getting a error because columnname2 exists only in database2 table and not in database1 table.
Error: Unknown column 'columnname2' in 'field list'

Database.php

$db['database2']['hostname'] = 'localhost';
$db['database2']['username'] = 'root';
$db['database2']['password'] = '';
$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';
$db['database2']['swap_pre'] = '';
$db['database2']['autoinit'] = TRUE;
$db['database2']['stricton'] = FALSE;

$active_group = 'default';
$active_record = TRUE;
$db['default']['hostname'] = 'localhost';
$db['default']['username'] = 'root';
$db['default']['password'] = '';
$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;

UPDATE: If i use the following

function __construct()
    {   
        parent::__construct();
        $this->load->database();
        $this->db_2 = $this->load->database('database2', TRUE);    
    }

database2 is taken as active by CI and all the queries are connected to database 2 even though $this->db->query(); is used to specify the queries.

Are you sure that the settings for `database2` are correct in your config file? You say that the `collumname2` field exists in database2 but the error says otherwise, so I can only guess that maybe both your variables (and config settings) for `$this->db` and `$this->db_2` connect to the same `db` where the `columnname2` doesn't really exist. — Rolando Isidoro, May 15 '13 at 08:25
@RolandoIsidoro Have included the database.php script fron config to the question. — curious_coder, May 15 '13 at 08:54
Try to execute `SHOW CREATE tablename;` or `DESC tablename;` on both connections and see if the result in what you expect and the `collumname2` field is listed for `$this->db_2`. — Rolando Isidoro, May 15 '13 at 08:57

score 1 · Answer 1 · answered May 21 '13 at 02:52

Calling both database instances in your constructor will cause one to override the other. The easiest way around this is to split your function and merge them back together.

function __construct() {
    $this->load->database('default', true);
}

function merge_results() {
    $result_a = $this->get_result_a();
    $result_b = $this->get_result_b();
    return array_merge($result_a,$result_b);
}

function get_result_a() {
    return $this->db->query("select columnname1 from tablename")->result();
}

function get_result_b() {
    $other_db = $this->load->database('db2',true);
    return $other_db->query("select columnname2 from tablename")->result();
}

Connecting two database in codeigniter not working

1 Answers1