Connecting to multiple databases on the same host is like connecting only once: searching a solution

[eluser]bAum[/eluser]
It appears that I stumbled across a problem very similar to what these two guys here discussed.

Consider the following code snippet:

Code:

$db2 = $this->load->database('db2', true);
$db2 = $this->load->database('db3', true);
      
$db2->select('something');
$db2->from('Staff');
$qry2 = $db2->get();
foreach($qry2->result() as $item) { /* do something */ }
      
$db3->select('something');
$db3->from('Project');
$qry3 = $db3->get();
foreach($qry3->result() as $item) { /* do something */ }

Where db2 and db3 are simply different DBs on the same database server (in my case MSSql 2005). db2 contains a table called Staff and db3 a table called Project.

The above snippet results in an error because supposedly the table Staff doesn't exist. Actually, it turns out that in the case of a persistent connection request, php will try to find a link that's already open with the same host, username and password. And if that's the case it will return this one instead of opening a new connection.

So what happens is that the db2 connection is established just normal. When php sees that the db3 connection is the same host, it returns a pointer to the db2 connection, yet it issues a "use db3" query to the database as part of the connection procedure. When the first query is then executed the database in use is db3 rather than db2 and the whole thing crashes.

What is strange is that this also happens in case of non-persistent connections.

My question is whether someone has come up with a transparent solution so that the above code would work (after all, it's a nice abstraction being able to say $db2->doThis and $db3->doThat)?

[eluser]bAum[/eluser]
I should have RTFM...

Even in case of non-persistent connections a second call to the connection function with the same parameters will yield the same link identifier. The optional new_link parameter of the connection function allows to override that which does the trick..

The new_link parameter appears to exist in some form for most databases (MSSQL, MySQL, PostgreSQL), so perhaps you might consider it for inclusion in the database parameter array so that one doesn't have to modify the CodeIgniter core for that.

[eluser]Derek Allard[/eluser]
bAum, that'd be very helpful. If you want to write up a bit more detail, I'll take a look for inclusion.