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:

Code: Select all

select row from table1,table2
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&#1111;'click'].' '.$row&#1111;'view']."<br>";
&#125;
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.