Welcome to WebmasterWorld Guest from 107.20.34.173

Forum Moderators: coopster & jatar k

Message Too Old, No Replies

Howto JOIN Tables Across Multiple Databases?

     

HoboTraveler

7:18 pm on Sep 10, 2007 (gmt 0)

10+ Year Member



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

7:50 pm on Sep 10, 2007 (gmt 0)

10+ Year Member



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

6:24 am on Sep 11, 2007 (gmt 0)

10+ Year Member



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

8:13 am on Sep 11, 2007 (gmt 0)

10+ Year Member



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

9:00 am on Sep 11, 2007 (gmt 0)

10+ Year Member




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

11:47 am on Sep 11, 2007 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



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

1:08 pm on Sep 11, 2007 (gmt 0)

10+ Year Member



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

2:15 pm on Sep 11, 2007 (gmt 0)

10+ Year Member



Hi Guys,

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

Thank-You

 

Featured Threads

Hot Threads This Week

Hot Threads This Month