15 most recent posts from 3 tables

jrolands

Ii have three different news tables (MySQL) news1, news2 and news3, each having columns ID Date and Text. How can I, using PHP, display on a single page the 15 most recent posts? (Total, not 15 from each table).

tomhath

Use a UNION to query all three tables and return a single result set, then use ORDER BY and LIMIT to get the 15 you want. Something like this (untested):

select ID, Date, Text from news1
UNION
select ID, Date, Text from news2
UNION
select ID, Date, Text from news3
ORDER BY date DESC
LIMIT 15

leatherback

HOwever, one might wonder why you have three new tables? Have you considered one table, with a colum identifying whatever is different between the tables?

jrolands

here are a lot more columns on the tables, and they vary from each of the tables..

leatherback

OK,

Just wanted to point out the -sometimes- obvious traps people walk into. If the rest of the tables are really different, consider my post as not posted.

jrolands

hehe.. thanks for caring though..

jrolands

does the array that the query create say anything about which table each post came from?

tomhath

You can add a column with the table name if needed:

select ID, Date, Text, 'news1' as tabname from news1
UNION
select ID, Date, Text, 'news2' as tabname  from news2
UNION 
...

Sxooter

If the tables are so very different, then how can you union them? If they have different columns, you could likely make a master table and simply insert nulls for the fields of a certain type. Nulls generally take up little or no space in a database, so there's not real issue with lost space, and this query becomes much easier to build / run. Alternatively, you could always have a master table with just the common fields in it, and then three child tables with the other rows in each one, and then join to them when needed. Again, it makes this summary stuff much easier to do.

Also, a quick point. Unions do a unique operation across the result sets you get back. I.e. if the result of union produced two rows that were the same, the union would throw one away. For larger datasets, this can be quite expensive, and normally isn't needed. Use UNION ALL to prevent the unique step from being performed and to speed up your unions.