1

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.

sree
  • 498
  • 4
  • 19
  • MySQL — Joins Between Databases On Different Servers - http://stackoverflow.com/questions/5832787/mysql-joins-between-databases-on-different-servers-using-python/5832973#5832973 – Devart Mar 06 '12 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 Mar 06 '12 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 Mar 06 '12 at 13:55
  • In this case it is enough to connect to one server. MySQL will connect to another server itself. – Devart Mar 06 '12 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 Mar 06 '12 at 14:15
  • Agree with Devart. Use [Federated storage engine](http://dev.mysql.com/doc/refman/5.1/en/federated-storage-engine.html) and `JOIN` or `UNION`. – Marcus Adams Mar 06 '12 at 14:31
  • ok i would give it a try.. tnx a ton!! :) – sree Mar 06 '12 at 17:44
  • Refer this: http://winashwin.wordpress.com/2012/08/22/mysql-federated-table/ –  Aug 30 '12 at 09:22

2 Answers2

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.

Ynhockey
  • 3,845
  • 5
  • 33
  • 51
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.