MySql or PHP?

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
daebat
Forum Commoner
Posts: 47
Joined: Mon May 11, 2009 10:16 am

MySql or PHP?

Post 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.
User avatar
AbraCadaver
DevNet Master
Posts: 2572
Joined: Mon Feb 24, 2003 10:12 am
Location: The Republic of Texas
Contact:

Re: MySql or PHP?

Post 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.
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.
daebat
Forum Commoner
Posts: 47
Joined: Mon May 11, 2009 10:16 am

Re: MySql or PHP?

Post 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?
User avatar
AbraCadaver
DevNet Master
Posts: 2572
Joined: Mon Feb 24, 2003 10:12 am
Location: The Republic of Texas
Contact:

Re: MySql or PHP?

Post 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.
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.
daebat
Forum Commoner
Posts: 47
Joined: Mon May 11, 2009 10:16 am

Re: MySql or PHP?

Post 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.
User avatar
AbraCadaver
DevNet Master
Posts: 2572
Joined: Mon Feb 24, 2003 10:12 am
Location: The Republic of Texas
Contact:

Re: MySql or PHP?

Post 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
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.
Post Reply