[SOLVED] Select from two mysql databases in one query

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
Mr Tech
Forum Contributor
Posts: 424
Joined: Tue Aug 10, 2004 3:08 am

Select from two mysql databases in one query

Post by Mr Tech »

Hey there,

I did a search and couldn't find anything.

I think I might have seen this before but can't remember...

If possible, how do I select information from two mysql databases in the one query?

Eg:

Code: Select all

select row from table1,table2
Cheers

Ben
User avatar
markl999
DevNet Resident
Posts: 1972
Joined: Thu Oct 16, 2003 5:49 pm
Location: Manchester (UK)

Post by markl999 »

Do you mean two databases or two tables?
Your example suggests two tables from the same database.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Code: Select all

SELECT field1, field2, fieldn FROM database1.table1, database2.table2
User avatar
Mr Tech
Forum Contributor
Posts: 424
Joined: Tue Aug 10, 2004 3:08 am

Post by Mr Tech »

markl999 wrote:Do you mean two databases or two tables?
Your example suggests two tables from the same database.
Sorry I got them mixed up. Yes, I needed to select two tables from the same database.

So feyd, is that code what I need after I just cleared that up?

If so, how would I display those in the code?

E.g:

Code: Select all

$query = mysql_query("SELECT field1, field2, fieldn FROM database1.table1, database2.table2");
while($row = mysql_fetch_array($query)) {
echo $rowїtable1.field1];
}
Thanks for the help :)
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

that's just the basic syntax.. if database1 and database2 are omitted, then it assumes the current seelcted database. Selecting from 2 tables requires attaching them together in some fashion, or you may get lots and lots of unexpected results..

read [mysql_man]JOIN[/mysql_man] for more information.
User avatar
markl999
DevNet Resident
Posts: 1972
Joined: Thu Oct 16, 2003 5:49 pm
Location: Manchester (UK)

Post by markl999 »

Another way is:

Code: Select all

$query = mysql_query("SELECT table1.field1, table2.field2 FROM database");
while($row = mysql_fetch_array($query)) {
echo $row['field1'].' '.$row['field2'];
}
If the two fields have the same name the you'll need to tweak, but we'll get to that if that's the case ;) As Feyd implied though, if you have a WHERE clause on the two fields then it can get icky and you might need to be careful.
User avatar
Mr Tech
Forum Contributor
Posts: 424
Joined: Tue Aug 10, 2004 3:08 am

Post by Mr Tech »

Thanks guys,

First of all I tried mars code an I'm getting this error:

Table 'ad_track.ad_track' doesn't exist

My query was:

SELECT ad_click.click, ad_view.view FROM ad_track where ad_click.time = '2005-01-07' and ad_view.time = '2005-01-07' and ad='18'

Secondaly I checked out http://dev.mysql.com/doc/mysql/en/JOIN.htm and found this peice of code which I think is what I'm looking for:

mysql> SELECT * FROM table1,table2 WHERE table1.id=table2.id;

if that is what I need, how would I display that on the page.

Cheers
User avatar
Mr Tech
Forum Contributor
Posts: 424
Joined: Tue Aug 10, 2004 3:08 am

Post by Mr Tech »

Aha! Worked it out. My code:

Code: Select all

$query = mysql_query("SELECT * FROM ad_click, ad_view where ad_click.time = '2005-01-07' and ad_view.time = '2005-01-07' and ad_click.ad='18' and ad_view.ad='18'") or die (mysql_error());
while($row = mysql_fetch_array($query)) {
echo $row&#1111;'click'].' '.$row&#1111;'view']."<br>";
&#125;
Seems to pick it up itself :)

Thanks guys! Couldn't have done it without you
User avatar
markl999
DevNet Resident
Posts: 1972
Joined: Thu Oct 16, 2003 5:49 pm
Location: Manchester (UK)

Post by markl999 »

Good job.
Post Reply