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't join them "on-the-fly" using neither PHP nor SQL, what you can do is fetch data from both hosts and compare/join them manually using PHP.

Answer 3

Its not possible, the only way its possible on PHP would be when your using clusters / several nodes with a replication setup.

Obviously you don't understand some of the more technical stuff in regards to mysql, but give this ago.

$master = mysql_connect("master_host","abc","abcdb");
$slave = mysql_connect("slave_host","xyz","xyzdb");

if(mysql_select_db("abc_db",$master) && mysql_select_db("xyz_db",$slave))
{
    //Both Selected
    $sql = mysql_query("SELECT * FROM employee",$master);
    $rows = array();
    while($row = mysql_fetch_assoc($sql))
    {
         //Query the other $slave DB here!
         $slave_sql = mysql_query("SELECT * FROM department WHERE department_id = " . $row['id'],$slave);
         while($sub_row = mysql_fetch_assoc($slave_sql))
         {
             $row = array_combine($sub_row,$row);
         }
         $rows[] = $row;
    }
}

But thats not what you want to be doing really.

Just incase your trying to join across 2 databases on the same server, as both your hosts in your code are locahost, this is possible

SELECT * FROM db1.employees db1_e,db2.records db2_r WHERE db1_e.employee_id = db2_r.record_eid

But not sever 1 to an external server without using replication, even then you can replication wont be much help.

References and links:

http://nathan.rambeck.org/blog/2-joining-mysql-tables-across-multiple-databases

http://dev.mysql.com/doc/refman/5.0/en/replication.html

Answer 4

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`