Suggestion for a efficient Pagination tutorial?

Question

I have looped data from mysql, and it's a pretty long list. What's the most efficient way to do pagination? Currently, I am looking at this one: http://www.evolt.org/node/19340

Feel free to recommend a better one, thanks !

Answer 1

I've been using the example in the book Wicked Cool PHP as my starting point. Very neat and well explained IMO.

Answer 2

Rather than fetching everything from the DB like in the article, you could just SELECT the rows you can actually display - ie. if you have 10 items per page, just select 10 - and then selecting the total amount of rows. If the DB is large this can be much more efficient even though it's two queries.

Answer 3

You want something like

SELECT * FROM your_table WHERE condition = true ORDER BY some_field LIMIT 100, 10

Where 100 is the number of records to skip and 10 is the number of rows to retrieve. Make sure you have an index covering condition and the order criteria fields if you want to have the maximum performance.

Answer 4

This is a really nice function/class to have as part of your standard library. I would strongly recommend you roll your own along these lines:

• Query to work out total items (rows).
• Code to work out upper & lower limit based on number of items you want to display per page.
• Second query LIMIT'ed accordingly.

I'd post some code, but that would take the fun out of it :)