I have more than 500 rows with in my Database Table with particular date.
To query the rows with particular date.
select * from msgtable where cdate='18/07/2012'
This returns 500 rows.
How to query these 500 rows by 10 rows step by step. Query First 10 Rows and show in browser,then query next 10 rows and show in browser?
This question is tagged with
~ Asked on 2012-09-18 13:26:46
Just use the
SELECT * FROM `msgtable` WHERE `cdate`='18/07/2012' LIMIT 10
And from the next call you can do this way:
SELECT * FROM `msgtable` WHERE `cdate`='18/07/2012' LIMIT 10 OFFSET 10
More information on
~ Answered on 2012-09-18 13:29:01
LIMIT limit OFFSET offset will work.
But you need a stable
ORDER BY clause, or the values may be ordered differently for the next call (after any write on the table for instance).
SELECT * FROM msgtable WHERE cdate = '2012-07-18' ORDER BY msgtable_id -- or whatever is stable LIMIT 10 OFFSET 50; -- to skip to page 6
Use standard-conforming date style (ISO 8601 in my example), which works irregardless of your locale settings.
Paging will still shift if involved rows are inserted or deleted or changed in relevant columns. It has to.
To avoid that shift or for better performance with big tables use smarter paging strategies:
~ Answered on 2012-09-18 13:55:05