The paged data showing on the web page is fetched from the MySQL database. To fetch data page by page, we can use LIMIT clause with the comma version or the OFFSET version.

The LIMIT clause restricts the number of rows the server returns to the client. For paging purpose (display the page links), we must obtain the total number of rows the SELECT statement would have returned if no LIMIT clause is used. To do this, we can issue a separate SELECT count(*) statement to get the total number of rows. MySQL also provides an alternative way - include a SQL_CALC_FOUND_ROWS option in the SELECT statement, and then invoke FOUND_ROWS() afterwards.

To learn how to use LIMIT clause, please refer to this tutorial.

In the following 3 queries, we mimic paging thru all the orders - return 10 rows of data in each query and move to next fetch by running another query.

You can view these queries like this:

1. The first query as page 1 which returns rows 1 - 10
2. The second query as page 2 which returns rows 11 - 20
3. The third query as page 3 which returns rows 21 - 30

-- Page 1: starting from the first record and return 10 records only.
select  * from orders
limit 0, 10;
 
-- Page 2: starting from the 11th record and return 10 records only.
select  * from orders
limit 10, 10;
 
-- Page 3: starting from the 21st record and return 10 records only.
select  * from orders
limit 20, 10;

Query 1: rows 1 to 10

Query 2: rows 11 to 20

Query 3: rows 21 to 30

Alternatively, you can resort to MySQL's LIMIT and OFFSET clause to accomplish the same thing.

-- Page 1: starting from the first record and return 10 records only.
select  * from orders
limit 10 offset 0;
 
-- Page 2: starting from the 11th record and return 10 records only.
select  * from orders
limit 10 offset 10;
 
-- Page 3: starting from the 21st record and return 10 records only.
select  * from orders
limit 10 offset 20;

Happy Coding!

Other tutorials in this category

1. Using String Functions, Part 1

2. Using String Functions, Part 2

3. Using String Functions, Part 3

4. Using MySQL Date and Time Functions, Part 1

5. Using MySQL Date and Time Functions, Part 2

6. Using MySQL Date and Time Functions, Part 3

7. Using MySQL Control Flow Functions

8. Using MySQL Conversion Functions

9. Using MySQL Comparison Functions

10. Using Aggregate Functions in MySQL

11. MySQL RANK() function

12. MySQL DENSE_RANK() function

13. MySQL ROW_NUMBER() function

14. Compare RANK(), DENSE_RANK(), and ROW_NUMBER() function

15. Using LIMIT, SQL_CALC_FOUND_ROWS and FOUND_ROWS() to Page Thru Query Result in MySQL

16. Use LEAST function to find the SMALLEST value from multiple arguments

17. How to make case-sensitive comparison in MySQL

18. Use GREATEST function to find the LARGEST value from multiple arguments

19. MySQL MID() function with examples

20. MySQL LOCATE() function with examples

21. MySQL SUBSTR() function with examples

22. MySQL POSITION() function with examples

23. How to use MySQL REPLACE function in SELECT and UPDATE queries

Back to Tutorial Index Page