How to Design a PHP API with Stable Pagination as Data Changes

A paginated response can be correct when it runs and still produce an inconsistent traversal. If an application requests a page, the dataset changes, and it then requests the next page, it may receive repeated items or miss others. This is a common problem with activity feeds, orders, and records that continue to grow.

Cursor pagination in a PHP API helps control this shifting, but it does not, by itself, guarantee a frozen view of the data. The important decision is to define what it means to advance through the list, what changes can occur during traversal, and what contract the client needs.

Why results change between pages

Suppose a query sorts records by date in descending order. The client retrieves the first 20. Before it requests the next page, three recent records are inserted. If the second request uses OFFSET 20, it starts at position 21 in the current dataset, not at position 21 as it was when the first request ran. Some items from the first page may appear again.

Items may also be omitted. If a record before the offset is deleted, later items move up one position, and a row the client expected to find may be left behind. The order is not necessarily stable if several rows share the same date, either: without an additional criterion, the database is not required to return those ties in the same order every time.

It is worth distinguishing two goals: avoiding shifts caused by changes in position and providing an exact snapshot of the entire dataset. A well-defined cursor pagination strategy helps with the first. The second requires an explicit consistency strategy, which may be more expensive and depend on the database.

Offset or cursor: choose based on the read pattern

Offset pagination, typically expressed with LIMIT and OFFSET, is simple and allows clients to jump directly to a known page. It may suit small or relatively static datasets, interfaces with frequent jumps between pages, and cases where inconsistencies during navigation are acceptable. With large datasets, high offsets may require the database to scan or discard many rows; the actual cost depends on the engine, indexes, and query.

Cursor pagination returns a reference to the point from which to continue, such as the last sort value and its unique key. The next query looks for records after or before that point instead of skipping a number of rows. It works well for sequential traversal, feeds, and lists that receive frequent inserts. In return, it does not naturally support jumping to an arbitrary page: the client must traverse pages or use another strategy.

The choice does not have to be universal across the entire API. You can expose offset pagination in an administrative query with numbered pages and cursor pagination in an activity feed. The interface should reflect what the server can guarantee, rather than promising both random navigation and absolute stability with a single mechanism.

Define a total order before creating the cursor

The cursor identifies a position only if the order is deterministic. Sorting solely by created_at is not enough when two records have the same date. Add a unique, immutable column as a tie-breaker, such as id:

ORDER BY created_at DESC, id DESC

This gives every row a defined position in the order. The continuation cursor must contain both values. With the same descending order, the next query looks for pairs smaller than the last pair returned:

WHERE created_at < :cursor_date
   OR (created_at = :cursor_date AND id < :cursor_id)
ORDER BY created_at DESC, id DESC
LIMIT :page_size

For ascending order, reverse the comparisons. With multiple criteria, the conditions must follow the complete lexicographic order: compare the first field, then the next if there is a tie. If directions are mixed—for example, descending date and ascending ID—each comparison must match the direction of its column. Do not simply reverse all the operators at once.

Sort values also need stable rules. If a column can be null, define how those values are sorted and encode that distinction in the continuation condition. It is preferable to use criteria that remain immutable during traversal: if a date that determines position changes, a row can move from one side of the cursor to the other.

Make the cursor opaque, validated, and bound to the query

A cursor can serialize the sort values and encode them, for example, with Base64URL. Opaque means the consumer does not need to interpret or construct it; it does not mean that Base64 protects it. If changing its values could alter the scope of the query, validate the format and sign the content with an HMAC or use an equivalent integrity mechanism. Do not include secrets or unnecessary personal data.

Validate types, expected fields, format version, and size limits before querying the database. Use SQL parameters for values. Column names and sort directions must not be accepted directly from the cursor or request; they must come from a server-side allowlist.

A date-and-ID cursor should not be accidentally reusable with different filters if that would produce a misleading continuation. You can include a canonical representation of the relevant filters, sort direction, and, where appropriate, page size, and sign them along with the continuation point. If they do not match the current request, return a clear error instead of silently continuing with a different query. In PHP, centralize encoding, validation, and signing to avoid duplicating rules across controllers.

Decide what consistency to offer when data changes concurrently

When traversing live data, each page queries the state available at that moment. A cursor based on an immutable order avoids many shifts caused by inserts before the point reached. But it does not create a snapshot: new rows may appear after the cursor, rows not yet visited may be deleted, or applicable permissions and filters may change. Document this behavior so the client does not mistake it for a closed export.

If the product needs all pages to represent a bounded dataset, one option is to fix a cutoff, such as a date or maximum ID, when traversal begins and add it to every query. This excludes inserts made after the cutoff when the chosen criterion allows it, but it does not preserve deleted rows or guarantee a perfect snapshot in the face of modifications. Another possibility is a transactional snapshot; keeping a transaction open across requests usually has operational and resource implications, so it should not be assumed to be the default solution.

The contract can state clear limits: supported ordering, filters that must remain unchanged, expiration if applicable, behavior for an invalid cursor, and whether concurrent changes can alter the dataset. Do not promise an absolute absence of duplicates or omissions if the strategy cannot guarantee it.

Test the boundaries and document the contract

Tests should verify the complete traversal, not just the shape of a response. Prepare rows with repeated sort values and check that concatenating several pages produces the expected order without duplicates. Include cases where the page size splits a group of tied values, and validate both ascending and descending order.

• Insert records before and after the cursor between two requests and check the agreed behavior.
• Delete a pending row and modify a sort column, if the model allows it; document the consequences.
• Change a filter, sort order, or traversal direction and check that an incompatible cursor is rejected.
• Send malformed, tampered, oversized cursors or cursors with values of the wrong types.
• Verify page-size limits and the no-results case, including the absence of a next page.

For diagnostics, log query-duration metrics, page size, and validation errors without dumping sensitive cursors or personal data. If repetitions occur, first check the total order and continuation condition. If the problem is query cost, inspect the execution plan and indexes on the sort fields and filter conditions.

Stable pagination does not depend on hiding a string in Base64: it depends on a deterministic order, consistent comparisons, controlled filters, and explicit expectations about concurrent changes. With these decisions in place, offset and cursor pagination become tools you can choose according to the traversal the client actually needs.