Jump to content

Archived

This topic is now archived and is closed to further replies.

semtex

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

Recommended Posts

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

Share this post


Link to post
Share on other sites
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.


Share this post


Link to post
Share on other sites
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?

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.