|
Custom Search
| |
|
Using LIMIT and OFFSET to Page Thru Query Result in MySQL When we have a large amount of data to show to the end users on a website, most likely we will break them down to different pages in a fashion like Prev/Next page or Page 1, 2, 3... This is normally known as paging thru records. 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:
-- Page 1: starting from the first record and return 10 records only.
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.
Happy Coding!
Other tutorials in this category 1. Using String Functions, Part 1 |
Copyright © 2024 GeeksEngine.com. All Rights Reserved. This website is hosted by HostGator. No portion may be reproduced without my written permission. Software and hardware names mentioned on this site are registered trademarks of their respective companies. Should any right be infringed, it is totally unintentional. Drop me an email and I will promptly and gladly rectify it. |
Home | Feedback | Terms of Use | Privacy Policy |