Issues with a Prev link

Question

Right now I have some links to move to a Previous page or to the Next page (or any page number).

ex. <--prev | 0 | 1 | 2 | 3 | next-->

My issues is that I am only listing items on the page with a "status='A'". (A for active... some items are no longer being listed on this page and would have a 'D' for Delete or 'S' for Sold Out).

In the SQL I have ... WHERE status='A' LIMIT $start_item, 20

My issues is with the Previous link. How should I calculate the $start_item for it? Right now I am using the current ID (the first ID on the page currently being displayed) and subtracting 20 from it (the number of items per page). But if there are any items that are not Active between the current ID and ID-20 then the 20 items being displayed when clicking on Prev will run into the current ID.

Answer 1

You don't need to know items' IDs at all. LIMIT works on the results after the WHERE filtering was applied, so all you need to know - is how many items per page you have (20 in this case). Then LIMIT 0, 20 will give you the first page of items; LIMIT 20, 20 - the second page, etc. The first number in LIMIT is not an id, but the number of item in the resulting set.

Answer 2

Based on your example, it's a little odd to see the page numbering start at zero. If you use the following:

start_item = (page_number - 1) * num_items_per_page

...you can display pages starting at 1.

My issues is with the Previous link. How should I calculate the $start_item for it?

From the sounds of things, you're passing the start_item in your request. I'd just use the page number, and calculate upon request. Then all you need in for your Prev and Next buttons is current_page_number - 1 and current_page_number + 1. It's also more accommodating if you decide to allow users to choose how many items per page.

Keep in mind that LIMIT has two parameters:

• starting row
• offset

...so your limit would resemble this psuedocode:

LIMIT [(page_number - 1) * num_items_per_page], 20

---

if there are any items that are not Active between the current ID and ID-20 then the 20 items being displayed when clicking on Prev will run into the current ID.

1. Because you have WHERE status = 'A', nonactive items (items whose status is not 'A') will never be returned from the query. That said...
2. Item state will change - they can be deleted or sold as you say, which will cause the data to shift. It's always possible that the current item is on the cusp of the page, and another items state being changed could cause the current item to now be on the previous page.