Paging Thru Data With "Previous" and "Next" Buttons

Consider the situation when you have to display pages of data (i.e. 10 rows per page), such as with a query like this:

SELECT * FROM mydata LIMIT 10,21

And, you want to display next/previous buttons.

The Problem

It's easy to know when there are more rows "previous" (if the first row number you're displaying is > 1, then there must be some previous rows).

However, it's not so easy to know whether there are more rows beyond the current one. Obviously, if your query returned less than 10 rows of data, you must have hit the end. But what if it returns exactly 10 rows? You really don't know if that was the last 10, exactly, or if there's more data lying beyond.

The Common Solution - And Its Drawbacks

The most common way of detecting more rows is by counting all the rows of data in the table, with something like:

SELECT COUNT(id) AS cnt FROM mydata GROUP BY id

And comparing that to the last row number you're displaying. If you are displaying rows 21..30, and the count is > 30, then you know you have more rows.

This works, however it requires an extra database query, separate from the one where you're read the 10 rows of data to be displayed.

A Better Solution

If only there were a way to combine the two needs together; query 10 rows of data, and detect if there's any more data after those 10 rows?

Well, there is - simply read 1 more row of data than you actually need!

To display 10 rows, try to read 11 rows! If you actually get 11 rows of data back, then

        1. throw away that 11th row

        2. display a "next page" button

because you know there's more data beyond the 10 rows of data you're displaying. If you got back exactly 10 rows (or less), you know you're at the end of the data, so you can display all the rows you read, but don't display a "next" button.

Why This is Good

This is a good trick for a few reasons. You're only performing 1 query instead of 2 (you're skipping the COUNT() query). This means you're not asking the database server to do as much work, and also you're getting all the results back quicker, displaying the resulting web page quicker.

Reducing the load on your database server is especially important as your quantity of data gets larger and larger.

Don't miss the latest sql tips and tricks!
Subscribe to our low-volume mailing list:

Privacy Policy

See other tricks:  Web | Unix | Perl | SQL | General