Joins with multiple MySQL databases

Anon

This may be simple, but I haven't figured out how to do it. I want to create a query that joins two tables from two DIFFERENT databases on the same MySQL server. Is this possible and if so, how?

I know how to do this with a straight MySQL Query but PHP requires you to connect to a specific Database before executing the query.

Any help would be much appreciated.

Brian

[deleted]

"straight" mysql also requires you to connect to a specific database. (that's why PHP requires it)

SELECT database_a.table_a.field_a, database_b.table_b.field_b
FROM database_a, database_b

works fine for me even inside PHP.

Anon

For an excellent tutorial on that try http://sqlcourse2.com/joins.html

Anon

Well, I guess I just didn't try removing the mysql_select_db() statement. Apparently, PHP automatically selects the DB if you don't manually do it before the query. Thanks for the help.

Anon

Here's an example that I used and it works:

SELECT
a.field1,
a.field2
FROM
database1.table a
LEFT JOIN
database2.table b
ON
a.field1=b.field1

Anon

What about connection file? I am confused. Here is my connection file.

$connection=mysql_connect("localhost","root","") or die ("Connection error.");
$db=mysql_select_db("buddy",$connection) or die ("Database error.");

$sql="SELECT
a.field1,
a.field2
FROM
database1.table a
LEFT JOIN
database2.table b
ON
a.field1=b.field1";
mysql_query($sql) or die(mysql_error());
Now how to define 2 different databases and how will the query select the database.

Thanks!

BestPriceHost.com
3 MySQL databases with every account