MYSQL Limit inconsistent

Question

$data = mysql_query("SELECT * FROM phpbb_posts ORDER BY post_id DESC LIMIT 4")
or die(mysql_error());

Above is my code and I'm having problems with the Limit part.
When I first set it, everything seems perfect, but I could come back in a few hours and look at the page again and a different number of records could be shown, even though the code is exactly the same.

Any ideas how I can fix that?

Answer 1

The data is different because more posts are made since you last checked the page. You're ordering by post_id, where the highest value will be the most recent post.

The easiest way keep the previous records is to cache the data - store it. But then the cache needs to know when to refresh that cache - otherwise it will never change.

If there's a date/time column, you could use that to filter the results before the LIMIT is applied. But it'd have to be wide enough to get at least four records. And the same problem with the dates to look at - at some point, you want those dates to change or the data served by the page will never change.

Answer 2

You don't specify how many records being returned are concerning you. The LIMIT should return a MAX of 4 records, fewer if there were not at least 4 that would be returned by your query. If you ever have MORE than 4 records returned then you have a problem.