homepage Welcome to WebmasterWorld Guest from 54.204.59.230
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor 2014
Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
Forum Library, Charter, Moderators: coopster & jatar k

PHP Server Side Scripting Forum

    
Howto JOIN Tables Across Multiple Databases?
HoboTraveler




msg:3446633
 7:18 pm on Sep 10, 2007 (gmt 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

 

charlier




msg:3446659
 7:50 pm on Sep 10, 2007 (gmt 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)

HoboTraveler




msg:3447044
 6:24 am on Sep 11, 2007 (gmt 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

charlier




msg:3447120
 8:13 am on Sep 11, 2007 (gmt 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...]

HoboTraveler




msg:3447149
 9:00 am on Sep 11, 2007 (gmt 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);

omoutop




msg:3447224
 11:47 am on Sep 11, 2007 (gmt 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)

charlier




msg:3447291
 1:08 pm on Sep 11, 2007 (gmt 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.

HoboTraveler




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

Hi Guys,

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

Thank-You

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved