Selecting from multiple tables in mySQL.

Each table has a different key.

[Chopster]

I need help in trying to retrieve information from different tables, each one having a different primary key (auto-increment).

I have 3 pages, each accessing their respective tables.

(TABLE_internet_arts)
TagID (PK)
name
email
message
date
ip
time
profile_id
site_url
image_url
msg_type
site_title
page_title

(TABLE_losangeles_arts)
TagID (PK)
name
email
message
date
ip
time
profile_id
site_url
image_url
msg_type
site_title
page_title

(TABLE_sanfrancisco_arts)
TagID (PK)
name
email
message
date
ip
time
profile_id
site_url
image_url
msg_type
site_title
page_title

Right now, for each table on it's own, the SELECT statement looks like the following:

SELECT * FROM internet_arts ORDER BY TagID DESC LIMIT $offset, $rowsPerPage";

What I want to do is take the messages from all 3 tables and have them displayed, ordering them in chronological order by the date & time posted onto one page.

Anyone know the correct syntax and how to make the SELECT statement to do this? Any help is appreciated.

[Dijkgraaf]

How about

SELECT * FROM internet_arts
UNION
SELECT * FROM losangeles_arts
UNION
SELECT * FROM sanfrancisco_arts
ORDER BY date desc, time desc
LIMIT $offset, $rowsPerPage

PS. Would be better practice to replace * with list of fields, that way if one table gets customised with an extra field it won't kill the SQL statement.

[Chopster]

Thanks Dijkgraaf.

That got them all on the same page, however, they are coming up in random order. Doesn't seem to be placing them chronologically by the date, time. Is there perhaps and attribute I'm missing in the ORDER BY that will do this?

[coopster]

Welcome to WebmasterWorld, Chopster and Dijkgraaf.

If you want to use an ORDER BY or LIMIT clause to sort or limit the entire UNION result, parenthesize the individual SELECT statements and place the ORDER BY and/or LIMIT after the last one.

(SELECT * FROM internet_arts) 
UNION 
(SELECT * FROM losangeles_arts) 
UNION 
(SELECT * FROM sanfrancisco_arts) 
ORDER BY date desc, time desc 
LIMIT $offset, $rowsPerPage;

[Chopster]

Thanks Coopster. Finally got around to trying it and it worked. Appreciate the help from both!

[Holzberg]

Actually, the best way to do this is to combine the three databases and add a field called location as varchar 20 and put the locations for each record in there (los angeles, san francisco, internet).

If you really wanted to have a "Normalized" database, you would need 2 tables...one for location which would hold a locationid (primary auto incrementing) and locationname. The table with the TagID would need a field named locationid (same primary key field as the location table) and that field would reference a record from the location table. This method reduces repeated data and also has more integrity. Just do a search for normalized databases in google.

have a nice day
:)