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));

stefandoorn · Accepted Answer · 2012-01-17 14:12:38Z

6

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

answered Jan 17, 2012 at 14:12

stefandoorn

1,0328 silver badges12 bronze badges

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

3 Comments

KuKu Over a year ago

query is not problem for me as such. my problem is to maintain the connections. across the database

stefandoorn Over a year ago

Make sure the same user/pass credentials are used for both databases. So your query can access them both. Else it is not going to work. Just leave out the $conn, or try what happens if you use any of them.

Pratik Butani Over a year ago

I loved this line "will only work with same credentials"

Community · Accepted Answer · 2023-11-17 19:59:20Z

0

The mysql module in the php environment doesn't support multiple database connections, and, besides, this module exists only for retrocompatibily reasons. You shouldn't use it.

mysqli and PDO are better, more complete (these support transaction and prepared statement, "mysql" don't), and PDO support also the multi-db query.

The old mysql is procedural only, mysqli can be used in procedural and OO programming, PDO is OOP.

edited Nov 17, 2023 at 19:59

CommunityBot

11 silver badge

answered Apr 7, 2014 at 22:22

Anarcociclista

3222 silver badges11 bronze badges

Collectives™ on Stack Overflow

Connecting multiple database and join query across database in php

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related