CODEWITHSUNDEEP
phpmysqldatabase-connectionmultiple-databases
sree
sree

Reputation: 498

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

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.

Upvotes: 1

Views: 8150

Answers (2)

user1252065
user1252065

Reputation:

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.

Upvotes: 3

Ynhockey
Ynhockey

Reputation: 3932

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.

Upvotes: 3

Related Questions