MySql or PHP?
Moderator: General Moderators
MySql or PHP?
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.
- AbraCadaver
- DevNet Master
- Posts: 2572
- Joined: Mon Feb 24, 2003 10:12 am
- Location: The Republic of Texas
- Contact:
Re: MySql or PHP?
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.
mysql_function(): WARNING: This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQLextension should be used. See also MySQL: choosing an API guide and related FAQ for more information.
Re: MySql or PHP?
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.
- AbraCadaver
- DevNet Master
- Posts: 2572
- Joined: Mon Feb 24, 2003 10:12 am
- Location: The Republic of Texas
- Contact:
Re: MySql or PHP?
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.
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.
mysql_function(): WARNING: This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQLextension should be used. See also MySQL: choosing an API guide and related FAQ for more information.
Re: MySql or PHP?
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.
- AbraCadaver
- DevNet Master
- Posts: 2572
- Joined: Mon Feb 24, 2003 10:12 am
- Location: The Republic of Texas
- Contact:
Re: MySql or PHP?
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 5mysql_function(): WARNING: This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQLextension should be used. See also MySQL: choosing an API guide and related FAQ for more information.