select inbetween elements in mysql?

Question

I am trying to implement the pagination in php. I am using the Mysql as back end database. I am trying to implement the pagination logic.

   I would be having lots of record. But the user will see only 10 at a time.

Now to show the first page, i do a SELECT * from USERS LIMIT 10. Now to get the next 10 and the subsequent 10 records i am not able to write a query. Please help me fetch the in between records to support pagination logic. Also provide if any other suggestions for pagination.

Answer 1

You should use the OFFSET option.

SELECT * FROM Users LIMIT 10 OFFSET 10 (or 20, or 30);

That way you just pass the start position in the request when you hit next (or the page number) and you'll retrieve the records you want.

Answer 2

MySQL's limit feature can take two arguments:

select * from USERS limit 10,10

The above would retrieve 10 rows starting at row 10. Bear in mind that the MySQL row offset is 0 based, not 1. The first argument is the starting row, the second is the page size.

Also, if your page size is consistent, all you need to do is pass in the current page (default to zero). That would then allow you to specify the start row as a page * size.