How to join two tables from two different databases using mysqli query?

Question

I have two tables from two different databases and I want to join them using php

How do you formulate this command: $result = mysqli_query(“which connection to use”,$query)?

here is the example:

//database1
$conn1 = mysqli_connect($host1, $user1, $pass1, $db1) or die();
//database2
$conn2 = mysqli_connect($host2, $user2, $pass2, $db2) or die();

$query = "SELECT * FROM database1.table1 JOIN database2.table2 ON database1.table1.column1 =  database2.table2.column2";
$result = mysqli_query($which_connection, $query) or die();
return $result;

I don't know why you have `or die()` there but you must read [How to get the error message in MySQLi?](https://stackoverflow.com/a/22662582/1839439) and [mysqli or die, does it have to die?](https://stackoverflow.com/a/15320411/1839439) — Dharman, Sep 26 '20 at 23:32

Mihail Minkov · Accepted Answer · 2020-09-27T00:09:29.937

0

In my experience the easiest this can be achieved is that the database user has privileges on both database schemas. So this way you could achieve what you require by using the database schema name in the queries. The only thing is that you have to be very careful not to omit it. So, with apostrophes and everything the query could end up looking something like:

SELECT * FROM `database1`.`table1`
JOIN `database2`.`table2` 
  ON `database1`.`table1`.`column1` = `database2`.`table2`.`column2`

The most important part is the user is granted privileges on both schemas.

Also, as Jose pointed below, the databases must be on the same database server.

edited Sep 27 '20 at 00:09

answered Sep 26 '20 at 23:11

Mihail Minkov

2,463
2
24
41

1

Both databases must be on the same server. – José Carlos PHP Sep 27 '20 at 00:03
Thanks for the comment, I added it to the answer. – Mihail Minkov Sep 27 '20 at 00:09

score -1 · Answer 2 · edited Oct 09 '20 at 15:14

-1

you can use this syntax for joining two tables

SELECT <...>
FROM A.table1 t1 JOIN B.table2 t2 ON t2.column2 = t1.column1;

A and B are two different databases.

edited Oct 09 '20 at 15:14

Aditya Tomar

841
1
13
20

answered Sep 27 '20 at 00:13

Monu Rohilla

1,311
5
20

How to join two tables from two different databases using mysqli query?

2 Answers2