Pagination using mysql and php

Question

Are there any MySQL queries that can help me retrieve x recent entries from a table having z total rows such that I can keep a take-off point y, where 0 <= x <= y <= z?

Actually its for pagination of information from a database, for example when a user hits the next button, the next most recent 5 (which is x here) posts, from newer to older posts, show each time the button is clicked.

I actually wrote a working algorithm using basic SELECT, UPDATE and loops for this but I am curious if there is a more efficient way.

Answer 1

Yes, there are many solutions.

If with "recent entries" you mean "sorted by date" (or with sequential indexed IDs), all you need to do is :

1. Define a variable called $last_id = 0;
2. Use a SELECT statement with a LIMIT clause (i.e. "SELECT * from Table where ID > $last_id order by ID LIMIT 20" for a 20 items page)
3. Save the last ID in a variable ($last_id = $myrow["ID"])
4. Repeat the search with the next page.
5. Use a similar algorithm to select previous pages.

Luigi