This is a general problem that I've run into again and again over the years, but never found a good solution.
I often need to display a webpage table of data, sortable by whatever column the user wants and whatever search value they entered. As a concrete example, right now I'm looking at a table of user accounts with the following columns: "first name", "last name", "account status", "last logged in". This it all stored in a single MySQL table. There's a search bar at the top where they can search by a specific string to limit the accounts displayed in the page. Very straightforward.
Now, when the user clicks on the "Edit" link for each item, I want that page to have "<< previous" and "next >>" links that will link to the appropriate account, as determined by the current search & sort.
So in other words, I want an (efficient!) MySQL query that you can pass the primary key ID of the current row and get back the one before it and the one after it. Seems like this should be easy, but I haven't been able to figure out a solution.
Any idea? Thanks in advance!