Part 1

For a little background on my issue and why I developed this solution see my previous post on Permalinks.

The problem:

I have a SQL SELECT statement with ORDER BY, OFFSET and LIMIT clauses. I take the result of the query and use it to present a paged record set via a GUI. After the users pages through the records (i.e., by clicking the GUI’s next page button to get the next set of records in the record set) and gets to the page that contains the record they are interested in, they can then select that record and “book mark” it. What I mean by “book mark” is they click a button and a static URL is generated so that they can later return to the URL and be right back at the same record. Additionally, the user can sort the records any way they want to, i.e., by filed A ascending or by field A descending and field B ascending or by field C ascending, field D descending, field A ascending and field B Descending, etc. Various filters can be applied to the record set in WHERE clause of the query as well.

The Solution:

When the user returns to the static URL presented above, the program needs to determine the offset of the previously selected record in the record set (as identified by its primary key ID in the static URL) when the same sort defined by the user (also in the static URL) is applied to the query. This will then allow us to determine 1) the offset of the first record on the same page as the selected record and 2) to determine the offset relative to the first record on the page. This needs to be done at the time the user returns to the static URL as additional records may be added to the record set or records may be deleted from the record set, i.e., there is no way to determine the offset of the selected record ahead of time because the number of records in the data set is dynamic and constantly changing. An example may help clarify things a bit.

Lets say on day 1 there are 300 records in our table and we display 25 records a page for a total of 12 pages. The offset of the first record on the first page would be 0 and the offset of the last record on the first page would be 24. The next page the first record would have an offset of 25, the last record, 49 and so on. Additionally besides the primary key, ID, our table has a name field and a age field.

Our user sorts the record set by the age field in descending order, navigates to the 5th page, selects the 16th record from the top and “book marks” it. The offset of the first record on the page is 25 x 5 – 1 = 124, the offset of the selected record is 25 X 5 + 16 – 1 = 140 and its offset from the top of the page is 140 modulo 25 = 15. This record has a primary key ID of 48, a name of Fred and an age of 35. The book marked static URL would contain the primary key id, the field(s) the user sorted the record set by and the direction of the sort (ascending or descending). The URL may look something like this:

http://www.example.com/pagedgui.php?id=48&sort1=age&dir1=desc

In the week that follows 150 records are added to the table and 50 records are deleted, for a total of 400 records (or 16 pages). 100 records with an age less than 35 were added, 30 records with an age less than 35 were deleted, with the remainder of the records added and deleted having an age greater than 35. The net results that when sorted by age, the record with a primary key id of 48 will now have an offset of 210 (140 + 100 – 30). It will now have an offset from the top of the page of 10 (the 11th record on the page… 210 modulo 25) and the first record on the page will have an offset of 200 (210 – 10 or 210 divided by 25 is 8 with a remainder of 10, then 8 x 25 = 200).

The following week our user returns by clicking on the URL. The real question is how do we NOW determine the offset of the record that he book marked ONE week ago?

For one possible solution see this post for Determine selected record offset for any ORDER BY sequence.

Or continue reading with How to determine the Record Offset given a primary key and a defined order by clause – Part 2