Retrieving Paginated Results with MySQL

by Curtiss   |   last updated August 4, 2009   |   PHP Tutorials ·

With MySQL, you can easily retrieve a specific set of results from within a larger recordset. This can be extremely useful if you are trying to break up a large set of results into separate pages. For instance, let’s say you have 2,000 records you want to display, but you would prefer to break them up into four pages of 500 results. To do this with MySQL, you write your SQL query the same way you normally would, but include the LIMIT command at the end of your query.

Let’s say your original query looks like:

SELECT * FROM tbl ORDER BY id;

Assuming that query normally retrieves a total of 2,000 results, you can limit that query so that it only returns 500 at a time. To do that, you would alter your query to look like:

SELECT * FROM tbl ORDER BY id LIMIT 500;

In the example above, the query will retrieve the first 500 results in the recordset. Then, to retrieve the next set of 500 results, you can add a second argument to the LIMIT command. That would look like:

SELECT * FROM tbl ORDER BY id LIMIT 501, 500;

In this example, MySQL will retrieve 500 records, beginning with the 501st record in the set.

Therefore, your pagination queries might look like:

SELECT * FROM tbl ORDER BY id LIMIT 500; // Retrieve the first 500 records
SELECT * FROM tbl ORDER BY id LIMIT 500, 500; // Retrieve records 501-1,000
SELECT * FROM tbl ORDER BY id LIMIT 1000, 500; // Retrieve records 1,001-1,500
SELECT * FROM tbl ORDER BY id LIMIT 1500, 500; // Retrieve records 1,501-2,000

The MySQL LIMIT clause accepts two parameters (the first parameter being optional). The first parameter indicates the record at which MySQL should start looking. For instance, if you plug in 276 as the first parameter, MySQL will start just after the 276th record (beginning with the 277th). The second parameter is the total number of records that should be retrieved in the set. If you just want MySQL to retrieve the first XX number of records, you can leave out the first parameter (which will be implied as zero) and just specify the total number of records you want to retrieve.

Because the LIMIT command is part of the MySQL command set, it can be used with any server-side language that supports MySQL. In other words, you can use these queries with VBScript, PHP, Perl and more.

It’s as simple as that. I hope that helps.