Welcome to WebmasterWorld Guest from 54.161.161.92

Forum Moderators: coopster & jatar k

Message Too Old, No Replies

Howto JOIN Tables Across Multiple Databases?

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

Junior Member

10+ Year Member

joined:July 22, 2005
posts:180
votes: 0


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

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

Junior Member

10+ Year Member

joined:Apr 12, 2003
posts:167
votes: 0


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)

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

Junior Member

10+ Year Member

joined:July 22, 2005
posts:180
votes: 0


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

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

Junior Member

10+ Year Member

joined:Apr 12, 2003
posts:167
votes: 0


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...]

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

Junior Member

10+ Year Member

joined:July 22, 2005
posts:180
votes: 0



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

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

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:Oct 15, 2004
posts:941
votes: 0


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)

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

Junior Member

10+ Year Member

joined:Apr 12, 2003
posts:167
votes: 0


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.
2:15 pm on Sept 11, 2007 (gmt 0)

Junior Member

10+ Year Member

joined:July 22, 2005
posts:180
votes: 0


Hi Guys,

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

Thank-You

 

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week

Featured Threads

Free SEO Tools

Hire Expert Members