How do I calculate the offsets for pagination?

Question

I am working on a pagination feature in a web service I am writing, but my lack of math insight is killing me now. I have a couple of keys: totalItems, currentItems, currentPage, totalPages, but also a couple of links to first, last, previous and next.

At this moment, I am doing the following calculations:

• totalItems: number of rows in the table
• currentItems: limit parameter from HTTP request
• currentPage: start parameter divided by limit parameter
• totalPages: number of rows in the table divided by limit. (Rounded up, 8.1 page = 9 pages)

I assume those calculations to be correct, what I am struggling with is the following:

• first: start parameter is 1 with limit from the HTTP request
• last: should be the first item of the last page, how do I calculate this correctly?
• previous: should be the first item of the previous page, how do I do this?
• next: should be the first item of the next page, how do I do this?

What I would like to ask, is: are my calculations correct? And how do I tackle the three problems with last, previous and next?

Answer 1

If you work with mysql its

LIMIT offset, items_per_page

To calculate the offset u can use

$offset = ($page - 1) * $items_per_page;

Then replace the $page accordingly.

Last

$last_offset = ($totalPages - 1) * $items_per_page;

Previous

$previous_offset = (($currentPage - 1) - 1) * $items_per_page;

Next

$next_offset = (($currentPage + 1) - 1) * $items_per_page;

EDIT :

if ($previous_offset > 0) echo '<a href="?start='.$previous_offset.'&limit='.$items_per_page.'>prev</a>';


if ($next_offset <= $totalPages * $items_per_page) echo '<a href="?start='.$next_offset.'&limit='.$items_per_page.'">prev</a>';

Answer 2

I have gone through lots of articles and create very simple formula for pagination

offset = (limit * page no) - limit

For example, if the limit is 5

LIMIT 5
    page 1 
        offset : no (No need to use offset)
        (5 - 1) - 5
         select * from users limit 5

    page 2
        offset : 5
        (5 * 2) - 5
        select * from users limit 5 offset 5

    page 3 
        offset 10
        (5 * 3) - 5
        select * from users limit 5 offset 10

    page 4 
        offset 15
        (5 * 4) - 5
        select * from users limit 5 offset 15

select * from table name limit 5 offset value (calculated from the formula)

this is worked for me

Answer 3

If for limit you are referring to the count of items per page, then:

currentItems: same as limit
currentPage: floor(start / limit)
totalPages: ceil(totalItems / limit)
last: totalPages * limit
previous: (currentPage-1) * limit // Should be greater or equal to 0
next: (currentPage+1) * limit // Should be less or equal than totalPages

It's just an approximation...