Howto JOIN Tables Across Multiple Databases?

[HoboTraveler]

Hi All,

I'm looking for a solution on howto join multiple tables across databases.

This bit of code does not work.. yet..

$SqlSelectQuery =
(
"
SELECT
a.ID,
a.Name,
b.ID,
b.Name
FROM
db1.a, db2.b
WHERE
a.ID = b.ID
"
);

$SqlSelectResult = mysql_query($SqlSelectQuery, $db1);
$SqlSelectResult2 = mysql_query($SqlSelectQuery, $db2);

PHP5
MySQL5

Is it possible to join tables across multiple databases?

TIA

[charlier]

You can do it with just one query.

select db1.tbl1.ID as ID1, db2.tbl2.ID as ID2 from
db1.tbl1,db2.tbl2
where ..... (must use db1.tb.field here as well)

[HoboTraveler]

Yes, but how do I trigger the mysql_query(); function? Do I specify both the db resource ID's by calling the function twice?

This does not seem to work.

$SqlSelectResult = mysql_query($SqlSelectQuery, $db1);
$SqlSelectResult2 = mysql_query($SqlSelectQuery, $db2);

TIA

[charlier]

You only need to open one link to the server. It is a server link and you can query any databases's on the server with that one link, only if you don't specify the database in the query it will use the default one which is the one used when opening the link or the the one from the the last use db command.

From the php annotated manual:

Description
resource mysql_query ( string $query [, resource $link_identifier] )

mysql_query() sends an unique query (multiple queries are not supported) to the currently active database on the server that's associated with the specified link_identifier.

[php.net...]

[HoboTraveler]

It is a server link and you can query any databases's on the server with that one link

I think I may be missing something here.

I'm calling two resource links that connect to two databases.

How do I merge these resource links and get mysql_query() to use them?

// Resource link, db1
$db1 = mysql_connect ("localhost", "user1", "password1") or die("Could not connect.");
mysql_select_db("db1", $db1);

// Resource link, db2
$db2 = mysql_connect ("localhost", "user2", "password2") or die("Could not connect.");
mysql_select_db("db2", $db2);

[omoutop]

I assume both databases are on same server.

The only way i have found is to use GRANT option to change priviledges between the two users of the two databases (assuming these are different users).

I grant one user access to both databases, and then run my queries using that user

You need to use the database prefix:
$query = "SELECT a.field1, b.field1 FROM database1.table1 as a, database2.table1 as b WHERE database1.table1.field2='' AND ..... ";

Hope this makes sence.
And i hope for a better solution to exist (its frustrating to change priviledges constantly)

[charlier]

Ah omoutop spotted it. I do most of my programming for the backend of a large site with a number domains each with their own database. However, I keep all the registrations on the site in one database and all the domains use it and their own database as well but the connection details are the same.

[HoboTraveler]

Hi Guys,

Thanks. Yes, I granted the user access to both databases and it works great!

Thank-You