mysql join query with tables in two different databases

Question

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

Are the two databases on the same server/database system, or on different servers? You can only join databases on the same connection/server. — aynber
– aynber, Commented Jun 7, 2022 at 18:09
If the two tables are on the same Database-Server, just other databases, you doesn't need two connections in php. Use Database.TableName. In your case ... FROM db1.table1 a JOIN db2.table2 b ... — user7884887
– user7884887, Commented Jun 7, 2022 at 18:37

Ergest Basha · Accepted Answer · 2022-06-07 19:35:01Z

1

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

answered Jun 7, 2022 at 19:35

Ergest Basha

9,3064 gold badges12 silver badges31 bronze badges

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

Collectives™ on Stack Overflow

mysql join query with tables in two different databases

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related