The Tool Desk
Outbyte PC Repair FREERepair Windows errors before they cause bigger problemsFix Now →Outbyte Driver Updater FREEFix the driver behind crashes, sound loss and screen glitchesFind Drivers →To get the previous and next record, navigate by its position in the listing’s ordered result—not by incrementing or decrementing its ID. Use the same filters and sort order as the listing, and add a unique tie-breaker such as id so records with identical names or company values still have a defined order.
Why id - 1 and id + 1 do not find adjacent records
An ID identifies a record; it does not tell you where that record appears in a list sorted by last name or company. For example, IDs 40, 55, and 25 could appear in that order after sorting by name. The previous and next records are the neighbors in the displayed result, regardless of their numeric IDs.
As an Amazon Associate I earn from qualifying purchases.
This is record navigation, not necessarily page navigation. Page navigation moves through chunks of results; record navigation moves to an item adjacent to the current record in a particular ordered result.
Do these 3 things before closing this tab:
1Repair Windows errors before they cause bigger problems2Fix the driver behind crashes, sound loss and screen glitches3Clear out junk files and repair common Windows errorsMake the listing order deterministic
Start with the exact ordering used by the listing, then add a unique key as the final tie-breaker. For example:
#1 Best Overall
ORDER BY last_name ASC, first_name ASC, id ASCIf two people share both names, id decides which one comes first. MySQL does not guarantee the relative order of rows tied on every expression in ORDER BY; adding a unique column makes the ordering deterministic. See the MySQL documentation on LIMIT optimization.
Use the same filters and ordering on the detail page that were used to produce the listing. Otherwise, “previous” may mean a different record after the user opens an item.
Rank #2
Choose how to find the neighbors
| Approach | When it fits | Trade-off to consider |
|---|---|---|
| Keep the ordered IDs from the listing | The detail page should preserve the exact order and filters the user just saw. | A long list of IDs is awkward to put in a URL, and the list can become stale if records or filters change. Validate any incoming IDs and decide how to handle deleted records. |
| Query by the current row’s sort position | The detail page can reconstruct the active filters and sort from trusted application state. | The query must account for every sort column, tie-breaker, direction, and the database’s NULL and collation behavior. |
Use an offset with LIMIT |
The application already has a stable position in the filtered, sorted result, or the result is small enough for this approach. | An offset is a position, not an ID. Inserts or deletions before that position can change which record occupies it. |
Option 1: Carry the ordered ID list
When a listing produces an ordered array of IDs, find the current ID’s index and take the IDs immediately before and after it. This mirrors the displayed order directly, including its filters, as long as the list remains valid.
- Build the listing with its filters and deterministic
ORDER BY. - Retain the ordered IDs in server-side state or pass an appropriately validated representation to the detail page.
- Find the current record’s position; use the prior and following IDs when present.
- Handle the first and last records, an ID that is absent from the list, and records deleted since the list was created.
A URL containing every ID may become unwieldy. If the list is large or must remain consistent across requests, keep the navigation context server-side rather than trusting a client-supplied sequence.
Option 2: Query by the full sort tuple
A neighbor query compares the current record’s ordering values with other rows, using the same filters and complete sort sequence as the listing. For last_name ASC, first_name ASC, id ASC, the next row is the nearest row that sorts after the current row under that full ordering. Comparing only last_name is not enough when multiple rows share a last name.
Think of the active sort as a fixed, validated configuration, rather than SQL text supplied by the request:
Rank #4
allowed_sorts = {
"last_name": [last_name ASC, first_name ASC, id ASC],
"company": [company ASC, last_name ASC, id ASC]
}
sort_order = allowed_sorts[validated_sort_key]
current_row = load row using a prepared parameter for its unique ID
previous_row = nearest row before current_row in sort_order
next_row = nearest row after current_row in sort_orderThis is pseudocode, not a drop-in SQL query. The correct comparison depends on your schema and ordering rules, including ascending versus descending columns, filters, collation, and how NULL values are treated. For mixed directions, the comparison for each component must follow that component’s direction. Test the first and last rows and cases where several rows have equal sort values.
Option 3: Use an offset when you have a position
MySQL’s LIMIT can return a row at a specified offset in an ordered result. The offset must refer to the same filtered and sorted result as the listing; it is not interchangeable with the current record’s ID. Use deterministic ordering so a position consistently refers to a row when the underlying data has not changed.
This can be convenient when the listing already knows the current ordinal. If records are inserted or removed between requests, the ordinal can shift. MySQL documents LIMIT behavior in its LIMIT optimization guidance; that documentation does not establish one approach as universally faster for every application.
Use current PHP database APIs safely
Do not copy historical examples that use mysql_* functions. PHP deprecated the original MySQL extension in PHP 5.5.0 and removed it in PHP 7.0.0. Current PHP applications should use MySQLi or PDO_MySQL; the PHP manual lists both as supported choices for new projects.
Bind values such as the current record ID through a prepared statement. A placeholder represents a data value, not an SQL identifier or keyword. Therefore, sort columns and directions should come from a fixed allowlist rather than being concatenated from arbitrary request input.
Quick Recap
- Use a prepared statement for the current ID and other data values.
- Validate the requested sort key against known choices such as
last_nameandcompany. - Map each allowed sort key to a complete order, including its unique tie-breaker.
- Apply the same filtering rules on the listing and detail navigation queries.
What to check when navigation is wrong
- It follows ID order: the query is comparing IDs instead of applying the listing’s sort order.
- Rows with the same name jump around: append a unique tie-breaker to the
ORDER BYand use it in neighbor selection. - Neighbors differ from the listing: compare the listing and detail-page filters, selected sort key, direction, collation, and NULL handling.
- It breaks after a data change: decide whether navigation should reflect the current result or preserve the original listing snapshot; offsets and stored ID lists can both become stale in different ways.
- The sort parameter causes SQL errors or unsafe behavior: treat it as a validated key into a fixed allowlist, not as SQL text.
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
