mysql join query with tables in two different databases-CodePudding

I am developing a php app based on MySQL. I connect to the first database with

$conn1 = mysqli_connect("127.0.0.1", "root", "password", "db1");

and then perform a query. How can I do the JOIN if table1 and table2 are located in 2 different databases (exemple $conn1 and $conn2) ?

$stmt = $conn1->prepare("SELECT a.var1, a.var2, a.var3, ...
                         FROM table1 a
                         JOIN table2 b //this is located on $conn2
                         ON a.var1 = b.var7

CodePudding user response：

the databases are on the same server (same harddisk)

You need only one connection, but the user you are using on the connection must have access on the both databases (or only on both tables).

In you query you must specify/append database names before each table.

Something like:

SELECT a.var1, a.var2, a.var3, ...
FROM database1.table1 a
JOIN database2.table2 b