Sanchan World Blog

Pagination using LIMIT clause in MySQL

MySQL supports a really cool feature for pagination is LIMIT clause.

The LIMIT clause is used to limit the number of results or rows returned in a SQL statement. So if you have 500 rows in a table, and only want to return the first 20, you would do something like this:

SELECT column1, column2, column3 FROM tablename LIMIT 20;

LIMIT clause always goes at the end of the query on MySQL. Now suppose if you wanted to show results 21-30.

SELECT * FROM tablename LIMIT 20,10;

This feature can be used in the pagination for multiple records. Pagination can achieved by passing the page number and number of results or rows per page in the coding.

SELECT * FROM tablename LIMIT $page_number*$rows, $rows; #php coding

For Example:
$rows = 10 (per page)
case page = 0: SELECT * FROM tablename LIMIT 0,10; #first 10 records
case page = 1: SELECT * FROM tablename LIMIT 10,10; #first 11-20 records
case page = 2: SELECT * FROM tablename LIMIT 20,10; #first 21-30 records
……
……