mysql - Connecting multiple database and join query across database in php

Question

Using php i wants to make a join query across 2 database.

This is my first connection.

$conn = mysql_connect('localhost','root1','pass1'); 
@mysql_select_db('database1',$conn);

This is my second connection.

$conn1 = mysql_connect('localhost','root2','pass2'); 
@mysql_select_db('database2',$conn1);

If i wants to get the data from database1 i am doing following thing.

$sql = 'SELECT * FROM users';

$result = mysql_query($sql, $conn);

print_r(mysql_fetch_array($result));

Similarly for second database2

$sql = 'SELECT * FROM orders';

$result = mysql_query($sql, $conn1);

print_r(mysql_fetch_array($result));

But i am facing problem when i am making join query as follows

$sql = 'SELECT a.fname AS fname, a.lname AS lname FROM database1.users a JOIN database2.orders b ON b.creator_id = a.id';

$result = mysql_query($sql);//what should be second parameter over here.

print_r(mysql_fetch_array($result));

Answer 1

You can do this by preceding the table name also with the database name, like you proposed in the example. But the logged on user needs to have access to both databases under the same credentials. This last part is very important, else you won't be able to do it.

This gives you an easy but straightforward example: link

Example taken from that link (will only work with same credentials):

SELECT
    c.customer_name,
    o.order_date
FROM
    db1.tbl_customers c LEFT JOIN
    db2.tbl_orders o ON o.customer_id = c.id