Page 1 of 1

MySql or PHP?

Posted: Wed Jan 20, 2010 1:30 pm
by daebat
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.

Re: MySql or PHP?

Posted: Wed Jan 20, 2010 1:40 pm
by AbraCadaver
Lacking a timestamp in the table, you could query each table ORDER BY id DESC LIMIT 5. Or if you want it all in one result set use a UNION in the query with the previous parameters.

Re: MySql or PHP?

Posted: Wed Jan 20, 2010 3:00 pm
by daebat
Lacking a timestamp in the table, you could query each table ORDER BY id DESC LIMIT 5.
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?

Re: MySql or PHP?

Posted: Wed Jan 20, 2010 3:05 pm
by AbraCadaver
daebat wrote:
Lacking a timestamp in the table, you could query each table ORDER BY id DESC LIMIT 5.
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?
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.

Re: MySql or PHP?

Posted: Wed Jan 20, 2010 3:16 pm
by daebat
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.
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.

Re: MySql or PHP?

Posted: Wed Jan 20, 2010 3:52 pm
by AbraCadaver
daebat wrote:
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.
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.
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:

Code: Select all

(SELECT * FROM t1)
UNION
(SELECT * FROM t2)
UNION
(SELECT * FROM t3)
ORDER BY date_added DESC LIMIT 5