Jump to content


Photo

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


  • Please log in to reply
2 replies to this topic

#1 semtex

semtex
  • Members
  • PipPip
  • Member
  • 11 posts

Posted 22 May 2006 - 11:32 AM

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



#2 eves

eves
  • Members
  • PipPip
  • Member
  • 26 posts

Posted 22 May 2006 - 12:51 PM

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.




#3 semtex

semtex
  • Members
  • PipPip
  • Member
  • 11 posts

Posted 22 May 2006 - 01:16 PM

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?




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users