Page 1 of 1
Select from two mysql databases in one query
Posted: Thu Jan 06, 2005 9:05 pm
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:
Cheers
Ben
Posted: Thu Jan 06, 2005 9:08 pm
by markl999
Do you mean two databases or two tables?
Your example suggests two tables from the same database.
Posted: Thu Jan 06, 2005 9:11 pm
by feyd
Code: Select all
SELECT field1, field2, fieldn FROM database1.table1, database2.table2
Posted: Thu Jan 06, 2005 9:40 pm
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

Posted: Thu Jan 06, 2005 9:45 pm
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.
Posted: Thu Jan 06, 2005 9:46 pm
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.
Posted: Thu Jan 06, 2005 10:10 pm
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
Posted: Thu Jan 06, 2005 10:14 pm
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ї'click'].' '.$rowї'view']."<br>";
}
Seems to pick it up itself
Thanks guys! Couldn't have done it without you
Posted: Thu Jan 06, 2005 10:18 pm
by markl999
Good job.