Multiple databases, codeigniter does not re-select the database

[eluser]dignick[/eluser]
Just to clarify, I have tested the following with Codeigniter 1.7.2 on php 5.3.2 + mysql 5.1.44 and php 5.3.2 + mysql 5.1.30.

Test code:

Code:

$test1['database'] = "test1";

...

$test2['database'] = "test2";

...

$db1 = $this->load->database($test1, TRUE);
$db2 = $this->load->database($test2, TRUE);
        
// DB2
$db2->set('test', 2);
$db2->where('id', 0);
$db2->update('test2');
        
// DB1
$db1->set('test', 1);
$db1->where('id', 0);
$db1->update('test1');

The original _execute() does not work:

Code:

function _execute($sql)
{
    $sql = $this->_prep_query($sql);

    $result = mysql_query('SELECT DATABASE()', $this->conn_id);
    $row = mysql_fetch_assoc($result);
    echo '$this->database = '.$this->database.'<br />';
    echo '$row[\'DATABASE()\'] = '.$row['DATABASE()'].'<br />';
    echo '$this->conn_id = '.$this->conn_id.'<br />';
    echo '$sql = '.$sql.'<br /><br /><br />';

    return @mysql_query($sql, $this->conn_id);
}

produces:

Code:

$this->database = test2
$row['DATABASE()'] = test2
$this->conn_id = Resource id #47
$sql = UPDATE `test2` SET `test` = 2 WHERE `id` = 0


$this->database = test1
$row['DATABASE()'] = test2
$this->conn_id = Resource id #47
$sql = UPDATE `test1` SET `test` = 1 WHERE `id` = 0

Note the $row['DATABASE()'] for both queries is the same.

Modifying _execute():

Code:

function _execute($sql)
{
    $sql = $this->_prep_query($sql);

    mysql_select_db($this->database, $this->conn_id);

    $result = mysql_query('SELECT DATABASE()', $this->conn_id);
    $row = mysql_fetch_assoc($result);
    echo '$this->database = '.$this->database.'<br />';
    echo '$row[\'DATABASE()\'] = '.$row['DATABASE()'].'<br />';
    echo '$this->conn_id = '.$this->conn_id.'<br />';
    echo '$sql = '.$sql.'<br /><br /><br />';

    return @mysql_query($sql, $this->conn_id);
}

produces:

Code:

$this->database = test2
$row['DATABASE()'] = test2
$this->conn_id = Resource id #47
$sql = UPDATE `test2` SET `test` = 2 WHERE `id` = 0


$this->database = test1
$row['DATABASE()'] = test1
$this->conn_id = Resource id #47
$sql = UPDATE `test1` SET `test` = 1 WHERE `id` = 0

success! $row['DATABASE()'] is as it should be.

Edit: I have just noticed that in the results above the connection id is the same, however when run on the other server (mysql 5.1.44) the resource id's are different...

Edit 2: Just realised that because all the other connection details are the same across both connections, php uses the same connection to mysql. Because of this, the different database names need selecting before each query. So a proper solution in CI would be either to:
a) in the database constructor, check if the resource id already exists in another database object, and if it does then enable database switching on query execution
or
b) do as I have done and switch database for every sql execution.

I may try to implement a) myself if I can.