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);

You cannot join across two connections, but you can join across databases on the same host - and you only need a single connection for that. See the end of @RobertPitt's answer. — Piskvor left the building
– Piskvor left the building, Commented Aug 27, 2010 at 9:18
Does this answer your question? Join tables from two different server — Nico Haase
– Nico Haase, Commented May 26, 2023 at 8:57

Community · Accepted Answer · 2017-05-23 12:19:48Z

7

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)

edited May 23, 2017 at 12:19

CommunityBot

11 silver badge

answered Aug 27, 2010 at 8:59

Piskvor left the building

93.1k46 gold badges182 silver badges226 bronze badges

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

2 Comments

Piskvor left the building Over a year ago

@RobertPitt: In this case, something not very useful - I yet have to find a real use case for that.

RobertPitt Over a year ago

Ibe just read some of the documentation and it does not seem to be a HUGE help, but I never knew about it so at least I know of it now :)

Marcis · Accepted Answer · 2010-08-27 09:10:03Z

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`

answered Aug 27, 2010 at 9:10

Marcis

4,61727 silver badges24 bronze badges

Comments

RobertPitt · Accepted Answer · 2010-08-27 09:25:20Z

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

David Kuridža · Accepted Answer · 2010-08-27 09:02:17Z

1

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.

answered Aug 27, 2010 at 9:02

David Kuridža

7,2875 gold badges29 silver badges25 bronze badges

Comments

Kev · Accepted Answer · 2011-07-01 03:29:49Z

0

Simply we can say. You can't run or join two tables of two different database in PHP.

you can run query on mysql prompt but not in .php file because every time you need to call database connection in mysql_query(); so you can't run two database at same time.

you have single choice just take first database's table's data in array and then run array loop or you can use it's id with IN in mysql or run two while loop.

edited Jul 1, 2011 at 3:29

Kev

120k53 gold badges308 silver badges396 bronze badges

answered Jun 30, 2011 at 21:15

Bagesh Singh

1

1 Comment

Kev Over a year ago

Please don't use sigs or taglines: Are taglines & signatures disallowed?. Thanks

Collectives™ on Stack Overflow

PHP-Mysql table join from different host

5 Answers 5

2 Comments

Comments

Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

2 Comments

Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related