how to return latest updates from different tables (w different field names)

[semtex]

I've made a mysql driven website that contains a few sections, such as review, interviews, news etc.

Each section has its own table in the database.

Each table has different fields of course.

Now I wanted my frontpage to show a list of the latest updated items. So the newest items should appear first (whether it's a review, a news item or an interview).

What's the best way to do this? I can't use this query as the fields differ for each table:

SELECT
title,
text
FROM
news,
reviews,
interviews
ORDER BY
created_dt DESC

[eves]

do the tables have a common relationship? if not it would be better to create a new table just for tracking the updates, here's a sample structure:
tbl_updates
- update_id - auto-increment
- link_id - store Primary Keys of tables on this field, wether its on reviews, interview or others
- type - type of update, review, interview, etc...
- tstamp - well, a timestamp

store updates on this table and just sort them by the tstamp field during retrieval and use the link_id and type fields to link them to their details



hope that helps.

[semtex]

Thanks Eves!

The tables do have a common relationship. They all have a field called "activated_dt", that stores a datetime. I could perfectly use "order by activated_dt desc" to order the updates.

Maybe I could use some query to generate the table name? If I have the table name and the ID I guess I know enough?

Is there a query or a way to return the table name?