Finding the position of a record in a paged result set

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)?

Current offset plus or minus one.

Huh? That doesn’t make any sense…

You have built a linked list which can be edited (added to, cropped). Great.

The next element is always the next element in the list until you hit the last one (+1).

The previous element is always the previous element in the list until you hit the first one (-1).

:smiley:

Correct, I already have that part implemented, getting the next and previous record in the list and it works with no issues. My problem is with figuring out what the position of the current record (element) is, relative to the entire dataset. For instance the total record count for the data set might be 500 records, however at any given point I am only storing an array of up to 100 records, and one of those 100 records is the currently viewed record. Using the known variables; the total number of records, the position of the record within the array subset, the page size, and current offset of the entire dataset, I need to figure out the record’s position, which could be something like “365 of 500”.

I see. I think we are having a problem with definitions. If we are talking about the “set found by the visitor,” a subset of the whole database, then my solution works. If you are trying to find the position of the current record in the whole database, then the question itself make no sense since the “working” sets can be in any user defined order different from the whole set. Should you nonetheless wish to find the position of the record in the original, unsorted, whole set, you have to get that number from the database itself – I don’t think it can be calculated.

It could be done with an array that contains the ids of every record, the cell number or key being the record’s position.

Edit: when I say: ‘the “set found by the visitor,”’ I’m ignoring the sub-sub-set that fits on a page. It’s the whole set found by the visitor which might take several pages to display.

Yes its the position in the record in the “set found by the visitor”. But I still don’t see any solution for my original problem, that is, calculating the position of the record (which will be displayed to the end user) relative to the entire set of records found by the user. The issue stems from the fact that the session-stored array of record IDs may only be a subset of the set of records that the user has found… so it alone cannot be used to determine the position of the current record. Example:

  • User views the first page of a list of 100 records, separated into 25 record pages.

  • When this first page is rendered the following pieces of information are all saved to the $_SESSION:

    • the record total (100)
    • the current offset (0)
    • the page size (25)
    • an array containing the IDs of the first page of records
    • the SQL queries used to get the records and the total record count
  • The user opens the detail view of the 24th record in the list, which should display the positional text of “24 of 100”

  • The user clicks “Next” from the detail view, to view the 25th record, which when rendered, determines that it is the last record ID in the currently stored array of record IDs, so, the original SQL query is run, but with its offset value increased by the size of the page, and the IDs of the next 25 records are appended to the end of the array, and the array and new offset value are both updated in the $_SESSION.

That last bolded part is key, in that the stored array of IDs is no longer identical in size to the page size, so I can no longer just do a simple calculation of query offset + index of current record in the array in order to determine the record’s position.

You don’t have to! Since “next” only advances by one, +1 is all you need. Since “previous” only retreats by one, -1 is all you need.

Right, but the whole point of doing the saving of data to the session, ect. was to reduce the number of queries being made to the database, and only getting +1 or -1 each time you go outside of the bounds of that original stored array of IDs means that then you are doing an additional query on each page view, instead of only 1 additional query every 25 page views.

I give up. :frowning: