php paging and the use of limit clause
- by Average Joe
Imagine you got a 1m record table and you want to limit the search results down to say 10,000 and not more than that. So what do I use for that? Well, the answer is use the limit clause.
example
select recid from mytable order by recid asc limit 10000
This is going to give me the last 10,000 records entered into this table.
So far no paging.
But the limit phrase is already in use.
That brings to question to the next level.
What if I want to page thru this record particular record set 100 recs at a time? Since the limit phrase is already part of the original query, how do I use it again, this time to take care of the paging?
If the org. query did not have a limit clause to begin with, I'd adjust it as limit 0,100 and then adjusting it as 100,100 and then 200,100 and so on while the paging takes it course. But at this time, I cannot.
You almost think you'd want to use two limit phrases one after the other - which is not not gonna work.
limit 10000 limit 0,1000 for sure it would error out.
So what's the solution in this particular case?
