PHP-Mysql table join from different host

Question

There is a table employee in the database abc_db at abc@localhost(server) and there is another table department in the database xyz_db at xyz@localhost(server). How can I join the tables using php mysql connection. I have written the following code but it does not generate any resource id.

$conn = mysql_connect("localhost","abc","abcdb");
$conn1 = mysql_connect("localhost","xyz","xyzdb");

$db_select=mysql_select_db("abc_db",$conn);
$db_select1=mysql_select_db("xyz_db",$conn1);

$sql="SELECT * FROM employee e LEFT JOIN department d ON e.department_id=d.id ";
$res=mysql_query($sql);

Answer 1

You can't join two tables using different connections to the database, not from PHP, nor on the MySQL server. (@RobertPitt has a good point: do you actually need two connections? It's possible to join two tables on the same host, but in different databases, within one connection - assuming your connection has the necessary privileges to access both)

If you have control over one or other of the databases, you might try setting up a federated table; make sure that the performance is OK though (if the db machines don't have a fast, low-latency connection (i.e. directly joined by a cable), don't bother), and there is a long list of limitations.

Possible lesser evils:

• replicate the table from one server to the other (tricky to set up)
• "join" them manually in PHP (gross, inefficient, but pretty much your only choice if you don't have control over the database)

Answer 2

You can make select between different databases and tables if used user has permissions to all of them (this is not possible if database hosts are different). This is just an example:

SELECT `table_a`.`column` AS `table_a_column`, `table_b`.`column` AS `table_b_column`
FROM `database_a`.`table` AS `table_a`
JOIN `database_b`.`table` AS `table_b` ON `table_b`.`smth` = `table_a`.`smth_else`