Multiple database connection to different hosts

netfrugal

I have 2 databases, on different host servers: users & users_addresses

I want to query both databases in 1 single query.

Here's the connections I've set up so far:

$dbh1 = mysql_connect($hostname, $username, $password); 
$dbh2 = mysql_connect($hostname, $username, $password, true); 

mysql_select_db('users', $dbh1);
mysql_select_db('users_addresses', $dbh2);

But the actual query gets confusing. Below I'm attempting to query both databases in a single query, based on 1 common uid, and I'm getting weirded out by what's the actual approach to it all.

$query = "SELECT tb1.*, tb2.* ";
$query .= "FROM users tb1, users_addresses tb2 ";
$query .= "WHERE  tb1.uid = tb2.uid ";
$qeury .= "AND tb1.uid = 5 ";
mysql_query($query);

Naturally this isn't working. Does anyone know if I need to specify a host inside the query itself? or will I need to add $dbh2 anywhere? Not finding much online about different host connections. But I was successful connecting on the same host, different databases.

sneakyimp

I'm not the uber database master (we can only hope that Sxooter shows up to help us out) but I don't think you can do that because [man]mysql_query[/man] generally assumes you are querying the last host you connected to with [man]mysql_connect[/man] OR you have to specify a link ID.

Sxooter

netfrugal;10909011 wrote:
I have 2 databases, on different host servers: users & users_addresses

Why 2 db servers?

sneakyimp

:eek:speak of the devil! That's uncanny.

Inkeye

You cannot query two DB's on two different hosts at the same time. Nope! Can't be done!

There's actually quite a bit you can't do on two different hosts. For example, you can't select records into another table on a different host...

If they absolutely have to be on two different hosts, then the only way you can do this is to make two intelligent queries: One that selects the possible data you might need from Host1, put it into an array in PHP, then PHP connects to the 2nd host, drops and recreates a tmp table, and puts the array in there, then run your 2nd query against the other table and this new tmp table now on the 2nd host.

And I would do that to whichever table had the least data to move around; and you will have enormous performance hit doing it that way (compared to having both tables on one host).

Sxooter

Inkeye;10909216 wrote:
You cannot query two DB's on two different hosts at the same time. Nope! Can't be done!

Well, there are workarounds for a lot of dbs. Oracle has some fancy syntax to do it. Pgsql has dblink to let you sorta kinda do it.

But just because you CAN do something doesn't mean you SHOULD...