MySql or PHP?
Posted: Wed Jan 20, 2010 1:30 pm
Is there a way to take an entire group of tables and display the five most recently added items on a page? All tables have an ID field that auto increments but they are not tied together.
A community of PHP developers offering assistance, advice, discussion, and friendship.
http://forums.devnetwork.net/
The problem is that each table has a separate ID field so I'll have several of the same number. I'm assuming time stamp is the best (and only) way to do this with my set up?Lacking a timestamp in the table, you could query each table ORDER BY id DESC LIMIT 5.
Depends upon what you want. How many tables? Is the field always ID or does each table have a different name for ID?daebat wrote:The problem is that each table has a separate ID field so I'll have several of the same number. I'm assuming time stamp is the best (and only) way to do this with my set up?Lacking a timestamp in the table, you could query each table ORDER BY id DESC LIMIT 5.
Lots of tables... like 25 to 30. Each has an ID field that is consistently named 'id'. In essence I would want to join all of the tables and then pull the 5 most recent posts... to me, knowing a little bit of database logic, this would only work with a time stamp.Depends upon what you want. How many tables? Is the field always ID or does each table have a different name for ID?
Also, do you want the 5 most recent from all tables, or the 5 most recent from table1 then the 5 most recent from table2, etc...
If you have the ability to add a date_added field, that would be the best either way.
Yes then, that's the way to do it. Add a date_added field of type DATETIME or TIMESTAMP and then use a union on the tables. Something like this may work assuming that you insert the current date/time when adding to the tables:daebat wrote:Lots of tables... like 25 to 30. Each has an ID field that is consistently named 'id'. In essence I would want to join all of the tables and then pull the 5 most recent posts... to me, knowing a little bit of database logic, this would only work with a time stamp.Depends upon what you want. How many tables? Is the field always ID or does each table have a different name for ID?
Also, do you want the 5 most recent from all tables, or the 5 most recent from table1 then the 5 most recent from table2, etc...
If you have the ability to add a date_added field, that would be the best either way.
Code: Select all
(SELECT * FROM t1)
UNION
(SELECT * FROM t2)
UNION
(SELECT * FROM t3)
ORDER BY date_added DESC LIMIT 5