Pagination turns a long result set into manageable pages. Search narrows the set before pagination is applied. The two features must share the same filters, ordering, and authorization rules, or the displayed totals and pages will disagree. This reference builds PDO MySQL LIMIT/OFFSET pagination and compares it with keyset navigation. You will validate page input, bind integer limits deliberately, escape LIKE patterns under a clear policy, and use a unique tie-breaker. Pagination is also a performance design: showing twenty rows does not necessarily mean the database only examines twenty rows.
Define a stable ordered result set
SQL does not guarantee row order without ORDER BY. Ordering by a nonunique name can still leave equal names in an arbitrary order, so add a unique tie-breaker such as id. Use the same order on every page. If search ranking is involved, include a deterministic secondary order where the engine permits it. Decide which rows the current user can access before applying pagination. A tenant or ownership predicate belongs in both the list query and count query. Do not paginate all private records and filter them afterward, because pages can become empty and totals can leak data. Document whether the result is a live view or a snapshot; concurrent inserts can change a live result between page requests.
Validate page and page-size parameters
A page parameter should be a scalar integer in an allowed positive range. Reject array-shaped input, invalid text, zero, and negative values under a clear policy. Set a modest maximum page size so a visitor cannot ask for a million rows. Calculate offset as (page - 1) times pageSize with range limits that avoid overflow. Choose how to handle a requested page beyond the end: an empty result or redirect under the interface contract. A default can suit missing input, but silently replacing a malformed supplied page may conceal bugs. Preserve filters in pagination links using safe URL construction rather than joining raw request values. Escape generated URLs when rendering them into HTML attributes.
Bind search and integer limits appropriately
Prepare the search value, not SQL fragments. LIKE pattern characters need a separate literal-versus-wildcard decision. For MySQL PDO native prepares, bind LIMIT and OFFSET values with PDO::PARAM_INT rather than relying on execute-array string conversion behavior. Use one named placeholder per occurrence under a policy that avoids driver reuse ambiguities. Dynamic sort choices need an allowlist mapped to reviewed ORDER BY fragments; they cannot be normal value placeholders. Select only required columns and cap the returned rows. Keep search terms out of SQL text even when you have trimmed them. Validation describes permitted input; preparation preserves the SQL syntax boundary.
Keep total counts consistent with filters
A COUNT(*) query needs the same search and authorization predicates as the page query. It usually does not need the page limit. Calculate total pages from the accepted page size and total count, while handling zero results explicitly. A count can be expensive for complex joins or large text searches, so consider whether the interface needs an exact total. Some products use a 'more results' flag by requesting pageSize + 1 rows rather than counting the entire set. Joins can multiply rows, so count the intended entities rather than accidentally counting joined combinations. If a product appears several times because of tags, use a query design that preserves the intended result unit.
Handle changing data and interface expectations
Between page requests, new rows can appear and rows can be deleted. OFFSET navigation may then repeat or skip items relative to the user's earlier page. Keyset navigation reduces some of those effects but still reflects a live dataset unless you define snapshot behavior. For exports requiring a consistent set, use an appropriate database snapshot or job-based selection policy. Show no-result states and out-of-range states clearly. Avoid exposing stack traces when a search fails. Rate-limit costly searches and monitor slow queries. Test repeated names, empty search, literal wildcard characters, the last partial page, and a deep page. These cases verify both correctness and the practical cost of navigation.
Practice task and worked solution
Build a product-search page that displays an exact total and next/previous links. The solution validates a positive page, limits page size to a fixed permitted set, uses the same literal-prefix search and ownership filters in COUNT and SELECT, and orders by name then id. Bind LIMIT and OFFSET as integers. Test zero results, eleven results with a size of ten, repeated names, an invalid page array, and a page beyond the end. Then sketch a keyset version using both name and id as cursor fields. Explain why the keyset version cannot naturally jump to numbered page one thousand without additional information.
Continue learning
Review PDO, CRUD, and REST APIs.
The PDO bindValue reference documents explicit parameter types.
Pagination and Search with SQL Examples
Each example shows the code, the output it produces and what each part does.
Bind MySQL page bounds as integers
Assume a configured PDO MySQL connection using native prepares and a products table. This fixed example requests the second ten-row page.
The response reports between zero and ten rows from matching positions eleven through twenty in name-and-ID order. Actual names and count depend on the table. This is MySQL LIMIT/OFFSET syntax, not a universal dialect example.
<?php
$page = 2;
$pageSize = 10;
$offset = ($page - 1) * $pageSize;
$term = 'Pen';
$pattern = strtr($term, ['!' => '!!', '%' => '!%', '_' => '!_']) . '%';
$stmt = $pdo->prepare(
"SELECT id, name FROM products WHERE name LIKE :pattern ESCAPE '!' "
. 'ORDER BY name, id LIMIT :size OFFSET :offset'
);
$stmt->bindValue(':pattern', $pattern, PDO::PARAM_STR);
$stmt->bindValue(':size', $pageSize, PDO::PARAM_INT);
$stmt->bindValue(':offset', $offset, PDO::PARAM_INT);
$stmt->execute();
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
echo count($rows) . ' rows on page ' . $page;Database-dependent: 0 to 10 rows on page 2Pagination and Search with SQL FAQ
Why do rows move between pages?
Live data changes and unstable ordering can both cause it. New or deleted rows shift every later row by one, and sorting by a column with duplicate values, such as a date, lets the database return tied rows in any order. Add a unique tie-breaker such as id to ORDER BY, and consider keyset pagination for live lists.
Can I bind ORDER BY as a value?
No. A placeholder in ORDER BY is treated as a constant value, so it doesn't choose a column. Use an identifier allowlist: map the user's choice, such as price, to a known column name in your code, and fall back to a default when the choice isn't in the list.
Does LIMIT make a query cheap?
Not necessarily. Filtering and offset work can still be large: the database may have to find, sort and skip thousands of rows before returning your 20, especially with a high OFFSET or without an index that matches the filter and order. Check the plan with EXPLAIN.
Must I count every result?
Only if the interface requires exact totals. COUNT(*) on a large filtered table can cost as much as the search itself. A "next page" link, which you can detect by fetching one extra row, or an approximate "more than 1,000 results" is often good enough.
Pagination and Search with SQL Exercises
Answer each one and check it instantly. If you get stuck, open the hint before the answer.
The list has a tenant filter but COUNT does not. What fixes it?
Pagination and Search with SQL Quiz
2 questions to check what you learned.
What gives stable order when product names repeat?
- No ORDER BY
- ORDER BY name, id with a unique ID tie-breaker
- Random browser sorting
- Only LIMIT
What can make deep OFFSET pages expensive?
- Integer binding alone
- The HTML page title
- Finding and discarding many earlier matching rows
- A shorter variable name