Queries from 2 databases at once

aleph_x

I'm having trouble querying 2 database at once. Hopefully this is possible in PHP/MySQL. Lets say the databases are called DB1 and DB2. They both contain a table called 'users' with 10 and 20 rows respectively. Consider the following code:

// -- start code --
$db1 = mysql_connect("host", "user", "pass");
mysql_select_db("DB1", $db1);

$db2 = mysql_connect("host", "user", "pass");
mysql_select_db("DB2", $db2);

$sql = "select * from users";

$r1 = mysql_query($sql, $db1);
$r2 = mysql_query($sql, $db2);

echo mysql_num_rows($r1) . "<br>";
echo mysql_num_rows($r2) . "<br>";
// -- end code --

When i run this page I get the output:
20
20

What i'm looking for is the output:
10
20

Can anyone help me?

aleph_x

BTW i realize my problem can be easily solved by connecting and querying one database first, then the next. But i need to be working with both databases at the same time so the above solution won't suffice.

Anon

Your problem is that you can only have one active connect to a database at the same time. Both the $db1 and $db2 variables are identifiers pointing to the same server, so only the latest mysql_select_db() statement is used. You will, instead, need to do this:

// -- start code --
$db = mysql_connect("host", "user", "pass");

$sql = "select * from users";

mysql_select_db("DB1", $db);
$r1 = mysql_query($sql, $db);

mysql_select_db("DB2", $db);
$r2 = mysql_query($sql, $db);

echo mysql_num_rows($r1) . "<br>";
echo mysql_num_rows($r2) . "<br>";
// -- end code --

I think that will work, unless you are working with 2 different servers, which I doubt. Hope this helps.

Anon

We had the problem in our test environment when migrating.

More than one connection to different databases is possible, but mysql worked curious.

If mysql found a connection to the same host, the same user and the same password (but different databases), it took that connection instead of opening a new one.

We solved our problem with other usernames for the different connections.

I'm not shure if that could be the same problem nor our problem is solved now in newer releases.

Charley