How to connect multiple database,servers in mysql and query from both tables of each other?

Question

I am looking forward to connect two different database from different servers. Also, i would want to run a query that fetches data from both the database into a single result. Am doing this in PHP script with mysql. here is how am looking forward to do it [ without success :) ]

$dbh1 = mysql_connect('server1', 'uname', 'pwd')or die("Unable to connect to MySQL1"); 
$dbh2 = mysql_connect('server2', 'uname', 'pwd') or die("Unable to connect to MySQL2");

mysql_select_db('db1', $dbh1);
mysql_select_db('db2', $dbh2); //both will have same table name though

$qry = mysql_query("select * from db1.table1 where db1.table1.id='100' and db1.table1.id=db2.table1.id",$dbh1) or die(mysql_error());

$row= mysql_fetch_array($qry);

echo $row[2];

Am not getting any result or either error. Any help is appreciated, tnx.

MySQL — Joins Between Databases On Different Servers - stackoverflow.com/questions/5832787/… — Devart
– Devart, Commented Mar 6, 2012 at 13:49
@Col. Shrapnel - Please revisit my question its much different from the one you have specified. i have researched that page and is still open in next tab of my browser!.. appreciate your guidance :) — sree
– sree, Commented Mar 6, 2012 at 13:52
@Devart the problem is am not able to connect two servers at same time. So ya if connected we can use Join.!! :) — sree
– sree, Commented Mar 6, 2012 at 13:55
In this case it is enough to connect to one server. MySQL will connect to another server itself. — Devart
– Devart, Commented Mar 6, 2012 at 14:05
As you can see in my code i do have one connection for now and am getting no result (or error). — sree
– sree, Commented Mar 6, 2012 at 14:15

Ynhockey · Accepted Answer · 2012-03-06 13:44:28Z

3

According to the PHP docs: http://il2.php.net/manual/en/function.mysql-query.php

"If the link identifier is not specified, the last link opened by mysql_connect() is assumed."

So in this case you're only retrieving data from $dbh2.

I don't think it's possible to do what you are trying to do with one query. You should merge the results after you get them.

answered Mar 6, 2012 at 13:44

Ynhockey

3,9515 gold badges37 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

sree Over a year ago

yep i know... need a diff solution :)

user1252065 · Accepted Answer · 2012-03-06 13:45:08Z

3

It doesn't work that way. You can use multiple databases in a single SQL query, but it always operates on one connection handle. If you need to connect to two different servers, you have to use two queries and merge data in PHP.

answered Mar 6, 2012 at 13:45

user1252065

Collectives™ on Stack Overflow

How to connect multiple database,servers in mysql and query from both tables of each other?

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related