|
| |
SQL Limit query for a range of records in MySQL table |
We may require to display some part of the records returned by a query specifying
a range. What is the range to be returned we can specify by saying
the starting and ending record number. We will pass this starting and
ending number along with the SQL LIMIT command to restrict the records within
that range. We will see the syntax of this query in our student MySQL table.
SELECT * FROM `student` LIMIT 0, 10
We have specified here to return 10 records starting from 0 or from the first
record. Same way we can ask for 10 records starting from 20th record like
this
SELECT * FROM `student` LIMIT 20, 10
This will return 10 records from 21st record. That is from 21st record to
30th record. Here is the output
| id |
name |
class |
mark |
| 21 |
Babby John |
Four |
69 |
| 22 |
Reggid |
Seven |
55 |
| 23 |
Herod |
Eight |
79 |
| 24 |
Tiddy Now |
Seven |
78 |
| 25 |
Giff Tow |
Seven |
88 |
| 26 |
Crelea |
Seven |
79 |
| 27 |
Big Nose |
Three |
81 |
| 28 |
Rojj Base |
Seven |
86 |
| 29 |
Tess Played |
Seven |
55 |
| 30 |
Reppy Red |
Six |
79 |
|
|
This is quite useful for designing paging in any script. Paging is known as displaying records in page wise with fixed number of records per page. There will be navigational menu to move between any pages and go to next and previous pages. The best example of php paging is the way google display search results. It display an easy navigational menu at the bottom of each search result page to go to next or previous page or any other page.
Note that this is returning number of records and this has no connection with the id number field used here. If the ID numbers are different then also the query will return 10 records starting from 21 record ( irrespective of the ID numbers ). If you want records with particular ID range specified then you have to use sql between command.
Download the SQL dump file for the student table here
| |
|
|
|
If you are facing any problem in viewing this page, please tell us