Ok, this question may be a little hard to explain, but it here it goes. I have a php/mysql web application which generates paginated lists of records contained in rather large mysql tables. From this list view (which can be ordered and filtered by the user), a user can open a detail view of any of the records on a new page, on which there are “Next” and “Previous” buttons to move to the next and previous records in the list (based on their current ordering and filtering of the list), w/o having to go back to the list view. The detail view also shows the position of the currently viewed record within the list, and the total number of records, i.e. “5 of 493” (may be a subset of the entire table if the list view was filtered).
Instead of re-executing the query used in the list view to find the ID’s of the next and previous records, as well as re-executing the count query to find the total number of records in the set each time a detail view is rendered, whenever these queries are executed from the list view itself, I store the queries themselves, offset, limit, total record count, and array of record ids in the current page to the $_SESSION. Then, once the user opens a detail view for a record from this list view, that session data is retrieved and used to determine what the next and previous record IDs are (by getting the record IDs adjacent to the currently viewed record’s in the stored array of IDs), and the position of the record in the entire result set (by using the offset value and the index of the record ID within the stored array).
Now, this next part of the implementation is what is causing me problems with finding the correct record position. In the event that the user clicks “Next” enough times to get to the last record in the session-stored array of record IDs (aka the last record in the page), at that point my detail page’s code first re-executes the stored list view query, increasing the offset by the size of the limit, and gets the next page of record IDs, which are then appended to the end of the stored array of IDs. And to avoid storing an overly big array in the session, the array size is kept at a maximum size of 100 elements, so if it surpasses this number, elements are chopped off of the beginning of the array. Likewise, when moving in the other direction (user is clicking “Previous”), pages of IDs are prepended onto the array as needed, and when the array size is greater than 100, the excess are chopped off the end of it.
This all works fine, but the issue is that this method of having an array of IDs that is potentially larger than the page size (limit) throws off my calculation of the record position. For instance, if I have result set of 30 records, and a page size of 25, If i start by viewing a record detail on page 1, and keep hitting next until I get to record 26, my current calculation will show this record as being record “51 of 30” instead of “26 of 30” like it should be. This is because my calculation is
current offset + index of record ID in stored array + 1
which for this record would be 25 + 25 + 1.
So what my question really boils down to is, what is the correct formula for determining the getting the correct record position at any point, using the available stored data, and accounting for the fact that the stored array of IDs can dynamically increase in size (up to a set maximum)?