Geek Answers Handbook

PHP-Mysql table joins from different hosts

The abc_db database has an abc @localhost (server) table employee, and the xyz_db database has another section of the xyz @localhost (server) table. How can I join tables using php mysql connection. I wrote the following code, but it does not generate a resource identifier.

$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);
+4
source share
5 answers

You cannot join two tables using different connections to the database, not to PHP, nor to the MySQL server. ( @RobertPitt makes good sense: do you really need two connections? Perhaps joining two tables on the same host, but in different databases, in the same connection - provided that your connection has the necessary privileges to access them)

If you have control over a particular database, you can try setting up a federated table; make sure the performance is okay though (if db machines don't have a fast connection with low latency (i.e. directly connected by cable), don't worry) and there is a long list of limitations .

Possible lesser evil:

  • copy a table from one server to another (difficult to configure)
  • manually attach them to PHP (rude, inefficient, but pretty much your only choice if you don't have control over the database)
+7
source

Impossible, the only way this is possible in PHP is to use your clusters / multiple nodes with replication settings.

Obviously, you do not understand some of the more technical characteristics regarding mysql, but give it earlier.

 $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 that is not what you want to really do.

Just try connecting to two databases on the same server, since 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 do not separate 1 to an external server that does not use replication, even then you may not use replication.

Links 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

+2
source

You can choose between different databases and tables if the user has permissions for all of them (this is not possible if the 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`
+2
source

You cannot join them on the fly using neither PHP nor SQL, what you can do is get data from both hosts and compare / combine them manually using PHP.

+1
source

We can just say. You cannot start or join two tables of two different databases in PHP.

you can run the query at the mysql prompt, but not in the .php file, because every time you need to call the database connection in mysql_query (); therefore, you cannot run two databases at the same time.

you have the only choice, just take the first data of the database table in the array and then run the array loop or you can use its id with IN in mysql or run two while loops.

0
source

More articles:


All Articles