Skip to content
Tutorials Center
My learning

Topics

Lesson 93 of 147 ยท Databases

Pagination, Search and Database Patterns

Learn practical PDO patterns - LIMIT/OFFSET and keyset pagination, safe LIKE search, sorting by whitelisted columns, avoiding N+1 queries, and migrations.

Knowing how to run a query is the start. Real applications need the same handful of patterns over and over: show results ten at a time, let users search, let them sort by a column they click, and load related data without hammering the database. Each of these has a naive version that works on your laptop with 20 rows and falls over in production with 2 million, or opens a security hole.

This lesson walks through the patterns professionals use, each as a small function you can reuse: offset and keyset pagination, search with properly escaped LIKE, sorting by a whitelisted column, spotting and fixing N+1 queries, and a tiny migration runner that keeps your schema under version control.

Every example uses PDO with an in-memory SQLite database, so you can run them here; the SQL works the same way in MySQL and PostgreSQL unless a note says otherwise. You should know prepared statements and CRUD with PDO.

Offset pagination with LIMIT and OFFSET

The simplest way to split results into pages is LIMIT (how many rows) and OFFSET (how many to skip). Page 1 skips 0 rows, page 2 skips perPage, page n skips (n - 1) * perPage. To draw "Page 2 of 5" links you also need the total row count, which takes a second query:

PHP Offset pagination

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with fgets(STDIN)), it gets the next line from this box.

The last page has only three products (21 to 23), and a request for page 99 is clamped to page 3 instead of showing an empty list. A few details carry over to every paginated page you build:

  • Validate the page number. $_GET['page'] is a string an attacker controls. filter_var(..., FILTER_VALIDATE_INT) returns false for garbage, and the ?: 1 turns that into page 1. See Handling Forms for the filter functions.
  • Bind LIMIT and OFFSET as integers. With MySQL's emulated prepares, a value bound as a string becomes LIMIT '10', which is a syntax error. bindValue(..., PDO::PARAM_INT) avoids it.
  • Always add ORDER BY. Without it, the database may return rows in any order, and the same row can appear on two pages.

Why OFFSET gets slow

OFFSET 100000 doesn't jump to row 100,001. The database finds and throws away the first 100,000 rows every time, so deep pages get slower and slower. Offset pagination is also unstable: if a new row is inserted while someone is reading page 1, every later row shifts down by one and page 2 repeats a row. For admin tables and search results that people rarely page deep into, offset pagination is fine. For infinite scroll, feeds and APIs, use keyset pagination.

Keyset (cursor) pagination

Keyset pagination remembers where the previous page ended and asks for rows after that point: WHERE id > :last_id ORDER BY id LIMIT 10. With an index on the sort column, the database jumps straight to the right place, so page 10,000 is as fast as page 1, and new rows never shift the pages.

When you sort by a column that isn't unique, like a price or a date, add the primary key as a tie-breaker and compare both columns. SQLite, MySQL and PostgreSQL all support row value comparisons such as (price, id) > (?, ?). The position is usually handed to the client as an opaque cursor string:

PHP Keyset pagination with a cursor

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with fgets(STDIN)), it gets the next line from this box.

The three products that cost 120 are ordered by ID, and the cursor after page 1 points between them, so nothing is skipped or repeated. Two tricks are worth copying: fetching perPage + 1 rows tells you whether a next page exists without a COUNT(*), and encoding the cursor hides its structure from clients, so you can change it later.

The trade-off: keyset pagination can't jump to "page 37", only to the next (or, with reversed comparisons, the previous) page. That's why it suits feeds and APIs better than numbered page links.

Search with LIKE

For a simple search box, LIKE with wildcards on both sides finds a term anywhere in a column: WHERE name LIKE '%lamp%'. The term must, of course, be a bound parameter. But there's a second, quieter issue: % and _ are wildcards inside the bound value, too. A user who searches for 50% or user_id gets wrong matches. Escape them, and tell the database which escape character you used:

PHP A LIKE search with escaped wildcards

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with fgets(STDIN)), it gets the next line from this box.

Without escapeLike(), the search for 50% would also match "Model 500 printer" (the trailing % matches anything after 50), and _ would match every product. Note that the PHP string '\\' is a single backslash; the Strings lesson explains escaping in quotes.

Case sensitivity depends on the database: SQLite's LIKE ignores case for ASCII letters, MySQL follows the column's collation (usually case-insensitive), and PostgreSQL's LIKE is case-sensitive (use ILIKE there).

A leading wildcard ('%lamp%') prevents the database from using a normal index, so every search scans the whole table. That's fine for thousands of rows. For large tables, use the database's full-text search (MySQL FULLTEXT indexes with MATCH ... AGAINST, PostgreSQL tsvector) or a search engine such as Meilisearch, Typesense or Elasticsearch.

Sorting by a column the user picks

Clickable column headers produce URLs like ?sort=price&dir=desc. You can't bind a column name or ASC/DESC as a parameter: placeholders only stand for values. So the column name has to go into the SQL string, which makes it an injection risk. The fix is a whitelist: map the allowed public names to real SQL, and ignore everything else.

PHP Sorting by whitelisted columns

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with fgets(STDIN)), it gets the next line from this box.

The malicious request falls back to sorting by name, ascending. Only strings you wrote yourself ever reach the SQL. The same rule applies to table names, LIMIT values you don't bind, and any other part of SQL that isn't a plain value. The whitelist also decouples your URLs from your schema: the public name price maps to the column price_cents.

The N+1 query problem

Here is a pattern that looks innocent: load a list of authors, then loop over them and load each author's posts. That's 1 query for the list plus N queries in the loop, the famous N+1 problem. With 50 authors it's 51 round trips to the database for one page. Every round trip to a MySQL server over a network costs time, so the page gets slow even though each query is fast.

The cure is to load the related rows for all authors at once, with a JOIN or with WHERE author_id IN (...), and group them in PHP. This example counts queries to show the difference:

PHP Finding and fixing N+1 queries

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with fgets(STDIN)), it gets the next line from this box.

The first version runs 6 queries for 5 authors; the second always runs 2, whether there are 5 authors or 5,000 (for very long ID lists, split them into chunks of a few hundred). ORMs call this eager loading: Laravel's with('posts') and Doctrine's fetch joins do exactly this under the hood. If you only need a count, a single GROUP BY query is even better: SELECT author_id, COUNT(*) FROM posts GROUP BY author_id.

To find N+1 problems in a real app, log or count queries per request, just like CountingDb does. Debug toolbars in frameworks show the number of queries on every page.

Migrations: version control for your schema

Your PHP code lives in Git, but what about the tables? If you change the schema by hand on your laptop, the staging server, and production, they drift apart. Migrations fix this: every schema change is a small, numbered script, and a table in the database records which ones have run. Deploying runs the pending ones in order, on every machine.

Frameworks include migration tools (Laravel's php artisan migrate, Doctrine Migrations, Phinx), but the core idea fits in a few lines:

PHP A tiny migration runner

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with fgets(STDIN)), it gets the next line from this box.

Each deploy runs only what's new. Date-based names sort naturally, so the order is clear even when several developers add migrations at once. Real tools add a "down" step to undo a migration and keep each migration in its own file. Remember from the Transactions lesson that MySQL commits implicitly on DDL, so there a failed migration can't be rolled back cleanly; that's one reason to keep each migration small.

Rule of thumb for migrations: never edit one that has already run on a shared server. Write a new migration that changes things further. Editing old ones makes environments disagree about what the schema is.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. With 10 rows per page, what OFFSET does page 4 use?

    OFFSET is (page - 1) * perPage, so page 4 skips the first 30 rows.
  2. 2. Why is keyset pagination faster than OFFSET on deep pages?

    WHERE (price, id) > (?, ?) lets the database jump straight to the right place in the index, while OFFSET N reads and discards N rows.
  3. 3. A user searches for 100% with name LIKE ? and the bound value '%100%%'. What is wrong?

    Binding prevents injection, but % and _ inside the value are still wildcards. Escape them and add ESCAPE to the SQL.
  4. 4. How should you handle a sort column that comes from the URL?

    Placeholders only stand for values, never identifiers. A whitelist guarantees that only column names you wrote reach the SQL.
  5. 5. A page lists 20 posts and runs one extra query per post to load its author. How many queries does it run?

    That's the N+1 problem - 1 query for the posts plus 20 for the authors. Loading all authors with one IN query brings it down to 2.

Summary

  • Offset pagination: LIMIT :perPage OFFSET :offset with offset = (page - 1) * perPage, a COUNT(*) for the page count, a validated and clamped page number, integers bound with PDO::PARAM_INT, and always an ORDER BY.
  • OFFSET gets slow on deep pages and shifts when rows are added. Keyset pagination (WHERE (col, id) > (?, ?)) is fast at any depth; pass the position as an opaque cursor and fetch one extra row to detect the next page.
  • LIKE search: bind the term, escape %, _ and the escape character, and add ESCAPE. Leading wildcards can't use indexes; use full-text search for big tables.
  • Sorting: identifiers and ASC/DESC can't be bound. Map user input through a whitelist with a safe default.
  • N+1 queries: one query per item in a loop. Load related rows for all items with IN (...) or a JOIN and group them in PHP (eager loading).
  • Migrations are numbered schema changes recorded in a table, so every environment runs the same changes in the same order.

Next: Web Security Basics