Dynamic APIs

Reviewed & published by Brayan K

By the end of this lesson you'll build a flexible "list" endpoint the client can shape from the query string — filtering, sorting, paginating, picking fields, and searching — built on dynamic SQL that's safe from injection, with a consistent, versioned JSON response.

Part of the free PHP course at LearnCodingFast — hands-on lessons with worked examples and the output they print, plus practice exercises and a quick quiz.

What You'll Learn in This Lesson

• Paginate results with limit/offset and return page metadata + navigation links
• Filter and sort a result set from query-string parameters
• Let clients pick fields (?fields=) and search across columns (?q=) safely
• Build a dynamic WHERE clause with bound parameters so injection is impossible
• Version an API (URL, header, query) without breaking existing clients
• Wrap every response in one envelope for success and errors alike

1️⃣ Filter, Sort & Paginate from the Query String

A good list endpoint never dumps every row. Instead it reads the query string — the ?key=value part of a URL, which PHP gives you in $_GET — and reshapes the result. Filtering keeps only matching rows, sorting orders them, and pagination hands back one slice at a time so a response stays small no matter how big the table is. Three rules keep this safe: only sort by allow-listed fields (never a raw field name), and always clamp per_page so nobody can request a million rows.

<?php
// A flexible "list" endpoint: the client decides what it gets back through the
// query string. We use an in-memory array so it runs anywhere; in a real app the
// same ideas become WHERE / ORDER BY / LIMIT on a PDO query (see section 4).

// Build a deterministic catalogue so the output is identical every time.
$products = [];
$categories = ['electronics', 'books', 'clothing'];
for ($i = 1; $i <= 50; $i++) {
    $products[] = [
        'id'       => $i,
        'name'     => "Product {$i}",
        'price'    => $i * 10,                 // 10, 20, 30, ...
        'category' => $categories[$i % 3],
    ];
}

// Pretend this came from $_GET on a request like:
//   GET /api/products?category=electronics&min_price=200&sort=price:desc&page=1&per_page=5
$query = [
    'category'  => 'electronics',
    'min_price' => 200,
    'sort'      => 'price:desc',
    'page'      => 1,
    'per_page'  => 5,
];

// 1) FILTER — keep only rows that match the supplied filters.
$rows = $products;
if (!empty($query['category'])) {
    $rows = array_filter($rows, fn($p) => $p['category'] === $query['category']);
}
if (isset($query['min_price'])) {
    $rows = array_filter($rows, fn($p) => $p['price'] >= (int) $query['min_price']);
}
$rows = array_values($rows);                    // re-index after filtering

// 2) SORT — "field:dir", only ALLOW-LISTED fields (never trust raw input).
$sortable = ['price', 'name', 'id'];
[$field, $dir] = array_pad(explode(':', $query['sort'] ?? 'id:asc'), 2, 'asc');
if (in_array($field, $sortable, true)) {        // ignore unknown fields safely
    usort($rows, fn($a, $b) =>
        $dir === 'desc' ? $b[$field] <=> $a[$field] : $a[$field] <=> $b[$field]
    );
}

// 3) PAGINATE — clamp per_page so a client can't ask for 1,000,000 rows.
$total    = count($rows);
$perPage  = min(max((int) ($query['per_page'] ?? 20), 1), 100);   // 1..100
$page     = max((int) ($query['page'] ?? 1), 1);
$pages    = max(1, (int) ceil($total / $perPage));
$offset   = ($page - 1) * $perPage;
$pageRows = array_slice($rows, $offset, $perPage);

echo "Matched {$total} products, showing page {$page} of {$pages}\n";
foreach ($pageRows as $p) {
    echo "  - {$p['name']}  \$" . $p['price'] . "\n";
}

Read the order top-to-bottom: filter → sort → paginate. That ordering matters — you sort the filtered set, then slice the sorted set, so page 1 is genuinely the "biggest 5 electronics", not 5 random rows that happened to sort well.

2️⃣ Pagination Metadata & Navigation Links

A bare array of rows leaves the client guessing: How many pages are there? Am I on the last one? Solve it by returning a meta block (current page, per-page, total count, total pages) and a links block with ready-made next/prev URLs. A null next-link is the cleanest possible "you've reached the end" signal — the client doesn't have to do any arithmetic.

<?php
// A list response should carry MORE than just the rows: it needs "meta" so the
// client knows where it is, and "links" so it can move without building URLs.

function paginate(array $items, int $page, int $perPage, string $path): array {
    $total   = count($items);
    $perPage = min(max($perPage, 1), 100);            // clamp: 1..100
    $pages   = max(1, (int) ceil($total / $perPage));
    $page    = min(max($page, 1), $pages);            // clamp into range
    $offset  = ($page - 1) * $perPage;

    // Helper that builds a page URL while preserving per_page.
    $link = fn(int $p) => "{$path}?page={$p}&per_page={$perPage}";

    return [
        'data' => array_slice($items, $offset, $perPage),
        'meta' => [
            'page'        => $page,
            'per_page'    => $perPage,
            'total'       => $total,
            'total_pages' => $pages,
        ],
        'links' => [
            'self'  => $link($page),
            'first' => $link(1),
            'last'  => $link($pages),
            'next'  => $page < $pages ? $link($page + 1) : null,   // null = no more
            'prev'  => $page > 1     ? $link($page - 1) : null,
        ],
    ];
}

$items = array_map(fn($i) => ['id' => $i, 'name' => "Item {$i}"], range(1, 23));
$response = paginate($items, 2, 3, '/api/items');   // page 2, three per page

header('Content-Type: application/json');                 // tell the client it's JSON
echo json_encode($response, JSON_PRETTY_PRINT);

Notice the paginate() helper clamps both per_page and page into legal ranges. Asking for page 99 of a 3-page list quietly returns page 3 rather than an empty, confusing payload.

3️⃣ Field Selection & Search

Field selection (partial responses) lets a client say "only send id and name" via ?fields=id,name. The win is twofold: smaller payloads for mobile clients, and a hard wall against leaking columns — you intersect the requested fields with an allow-list, so a private column like password_hash can never be requested. Search is just a filter that matches text across one or more columns, usually case-insensitively with str_contains().

<?php
// Field selection lets the client say "only send me these columns" with ?fields=.
// It shrinks the payload AND protects you from leaking sensitive columns.

$user = [
    'id'            => 7,
    'name'          => 'Alice Smith',
    'email'         => '[email protected]',
    'password_hash' => '$2y$10$abc...',     // must NEVER be sent to a client
    'is_admin'      => true,
];

// Only these may ever leave the server. Anything not on the list is invisible.
$publicFields = ['id', 'name', 'email'];

// Client asked: GET /api/users/7?fields=id,name
$requested = explode(',', 'id,name');

// Keep only fields that are BOTH requested AND public (allow-list wins).
$allowed = array_values(array_intersect($requested, $publicFields));
$partial = array_intersect_key($user, array_flip($allowed));

echo "Partial response (?fields=id,name):\n";
echo json_encode($partial, JSON_PRETTY_PRINT) . "\n\n";

// === Search across multiple fields with ?q= ===
$posts = [
    ['id' => 1, 'title' => 'Learning PHP', 'body' => 'PHP powers the web'],
    ['id' => 2, 'title' => 'CSS Grid',     'body' => 'Layout made easy'],
    ['id' => 3, 'title' => 'PHP and JSON', 'body' => 'Encode arrays fast'],
];
$q = 'php';
$hits = array_values(array_filter($posts, function ($p) use ($q) {
    $needle = strtolower($q);
    return str_contains(strtolower($p['title']), $needle)   // case-insensitive
        || str_contains(strtolower($p['body']),  $needle);
}));

echo "Search ?q=php matched " . count($hits) . " posts:\n";
foreach ($hits as $p) {
    echo "  #{$p['id']} {$p['title']}\n";
}

Now you try. The script below has the pagination maths almost done — fill in each ___ using the 👉 hint, then run it and check it against the Output panel.

<?php
// 🎯 YOUR TURN — finish the pagination maths, then run it.
// You have 47 items and want page 3 with 10 per page.

$total   = 47;
$page    = 3;
$perPage = 10;

// 1) How many pages are there? (round UP)
$totalPages = ___;          // 👉 max(1, (int) ceil($total / $perPage))

// 2) How many rows do we SKIP to reach this page?
$offset = ___;              // 👉 ($page - 1) * $perPage

// 3) Is there a next page?
$hasNext = ___;             // 👉 $page < $totalPages  (a true/false comparison)

echo "Pages: {$totalPages}, offset: {$offset}, has_next: " . ($hasNext ? 'yes' : 'no') . "\n";

// ✅ Expected output:
//    Pages: 5, offset: 20, has_next: yes
?>

4️⃣ Safe Dynamic WHERE Building

This is the section that keeps you out of the news. The moment your WHERE clause depends on user input, the temptation is to glue strings together — and that's exactly how SQL injection happens. The safe pattern has two halves. The SQL text (column names, operators, the word AND) comes only from an allow-list you wrote. The values go into bound :placeholders that PDO escapes for you. Because the user's input never becomes part of the SQL string, even a hostile value is treated as plain data.

<?php
// THE most important pattern in this lesson: build a WHERE clause from optional
// filters WITHOUT ever concatenating user input into the SQL string.
//
// The trick: the COLUMN NAMES come from an allow-list you control, and the
// VALUES go into placeholders (:name) that PDO binds and escapes for you.

function buildProductQuery(array $query): array {
    // Allow-list: maps a safe query-param name -> a real "column operator".
    $filters = [
        'category'  => 'category = :category',
        'min_price' => 'price >= :min_price',
        'max_price' => 'price <= :max_price',
    ];

    $where  = [];   // pieces of SQL, e.g. "price >= :min_price"
    $params = [];   // values to bind, e.g. [':min_price' => 100]

    foreach ($filters as $key => $clause) {
        if (isset($query[$key]) && $query[$key] !== '') {
            $where[]            = $clause;            // SQL text is OURS, never the user's
            $params[":{$key}"]  = $query[$key];      // the VALUE is bound, not pasted
        }
    }

    $sql = 'SELECT id, name, price FROM products';
    if ($where) {
        $sql .= ' WHERE ' . implode(' AND ', $where);
    }

    // Sorting also via an allow-list — you cannot bind a column name.
    $sortable = ['price' => 'price', 'name' => 'name'];
    [$f, $dir] = array_pad(explode(':', $query['sort'] ?? ''), 2, 'asc');
    if (isset($sortable[$f])) {
        $sql .= ' ORDER BY ' . $sortable[$f] . ($dir === 'desc' ? ' DESC' : ' ASC');
    }

    $sql .= ' LIMIT :limit OFFSET :offset';          // always bound a page
    $params[':limit']  = min(max((int) ($query['per_page'] ?? 20), 1), 100);
    $params[':offset'] = (max((int) ($query['page'] ?? 1), 1) - 1) * $params[':limit'];

    return [$sql, $params];
}

// Even a hostile value is harmless — it's bound, never executed as SQL.
[$sql, $params] = buildProductQuery([
    'category'  => "books'; DROP TABLE products; --",
    'min_price' => 10,
    'sort'      => 'price:desc',
    'page'      => 2,
    'per_page'  => 25,
]);

echo $sql . "\n\n";
echo "Bound params:\n";
foreach ($params as $k => $v) {
    echo "  {$k} => " . var_export($v, true) . "\n";
}

// In a real app you'd then run:
//   $stmt = $pdo->prepare($sql);
//   $stmt->execute($params);   // PDO safely binds every value
//   $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

Look closely at the output: the malicious category value lands in :category as an ordinary string — it is bound, not executed. Two things you cannot bind are column names and sort direction, so those must always pass through an allow-list (here a $sortable map), never straight from $_GET.

Your turn again. Build a small safe WHERE clause: the SQL piece comes from the allow-list, and the value goes into a bound placeholder.

5️⃣ Versioning & a Consistent Envelope

Versioning lets you change a response shape without breaking apps already using the old one — rename a field in /api/v2/ while /api/v1/ keeps working untouched. URL-path versioning is the most common because it's obvious and cache-friendly. Pair it with one response envelope — the same success, data, errors, meta shape on every endpoint — so a client writes a single parser and reuses it everywhere, for both successes and failures.

<?php
// API versioning lets you change a response shape WITHOUT breaking old clients.
// Three common places to put the version:
//   1) URL path:      GET /api/v1/users       (most common, cache-friendly, obvious)
//   2) Accept header: Accept: application/vnd.myapp.v2+json
//   3) Query string:  GET /api/users?version=2

function apiV1User(int $id): array {
    return ['id' => $id, 'name' => 'Alice Smith', 'email' => '[email protected]'];
}

function apiV2User(int $id): array {
    return [
        'id'         => $id,
        'first_name' => 'Alice',                 // v2 split "name" into two fields
        'last_name'  => 'Smith',
        'email'      => '[email protected]',
        'created_at' => '2024-01-15T10:30:00Z',
    ];
}

// ONE envelope for every endpoint = clients write ONE response parser.
function apiResponse(mixed $data, array $meta = [], ?array $errors = null): array {
    return [
        'success' => $errors === null,
        'data'    => $errors === null ? $data : null,
        'errors'  => $errors,
        'meta'    => array_merge(['version' => 'v2'], $meta),
    ];
}

$version = 'v1';                                  // would come from the URL/header
$user    = $version === 'v1' ? apiV1User(1) : apiV2User(1);
echo "GET /api/{$version}/users/1\n";
echo json_encode(apiResponse($user), JSON_PRETTY_PRINT) . "\n\n";

// The SAME envelope carries errors — note success:false and data:null.
echo "Validation error (same shape):\n";
echo json_encode(apiResponse(null, [], [
    ['field' => 'email', 'message' => 'Email is required'],
]), JSON_PRETTY_PRINT);

Common Errors (and the fix)

• SQL injection from a dynamic query — you built the WHERE by concatenating $_GET, e.g. "... WHERE category = '" . $_GET['category'] . "'". A value like '; DROP TABLE products; -- then runs as SQL. Fix: column names/operators come from an allow-list; values go into bound :placeholders and $stmt->execute($params).
• Unbounded result set — SELECT * FROM orders with no LIMIT returns 500K rows, exhausts memory, and times out. Fix: always apply LIMIT/OFFSET, default per_page to ~20, and clamp it (e.g. min($perPage, 100)) so a client can't override the cap.
• Inconsistent pagination — page 2 silently overlaps page 1 because you sorted by a non-unique column (lots of equal prices). Fix: add a stable tiebreaker like ORDER BY price DESC, id ASC so every row has a single, deterministic position.
• Leaking fields — json_encode($row) ships the whole row, including password_hash or is_admin. Fix: select only the columns you mean to return, and run output through a public-field allow-list with array_intersect_key — never blacklist.
• "Invalid parameter number" from PDO — you used a placeholder in the SQL that isn't in your params array (or vice versa). Fix: only add a :name to the SQL in the same branch where you add it to $params, exactly as the allow-list loop does.

Pro Tips

• 💡 Allow-list, never blacklist. Decide what's permitted (sortable columns, public fields, valid filters) and reject everything else. Blacklists always miss a case.
• 💡 Clamp every number from the client. page, per_page, min_price — cast to (int) and bound the range so input can't blow up a query.
• 💡 Use cursor pagination for big public feeds. ?after=12345 stays fast at any depth because the database never counts/skips earlier rows the way OFFSET does.

📋 Quick Reference — Dynamic APIs

Mini-Challenge: Search Books

No code is filled in this time — just a brief and an outline. Combine everything from this lesson (search, filter, sort) into one small endpoint, run it on onecompiler.com/php or your own machine, then check your result against the expected output in the comments.

<?php
// 🎯 MINI-CHALLENGE: A flexible "search books" endpoint.
// No code is filled in — work from the steps, then run it.
//
// Start from this catalogue:
//   $books = [
//     ['id'=>1,'title'=>'PHP Basics','price'=>30,'genre'=>'tech'],
//     ['id'=>2,'title'=>'Cooking 101','price'=>20,'genre'=>'food'],
//     ['id'=>3,'title'=>'PHP Advanced','price'=>50,'genre'=>'tech'],
//   ];
//
// Given this query:  ['q'=>'php', 'genre'=>'tech', 'sort'=>'price:desc']
// 1. SEARCH: keep books whose title contains $query['q'] (case-insensitive —
//    use strtolower + str_contains).
// 2. FILTER: if 'genre' is set, keep only matching books.
// 3. SORT: by price descending when sort is 'price:desc' (use the <=> operator).
// 4. echo each surviving book as "  #id title \$price".
//
// ✅ Expected output:
//    #3 PHP Advanced $50
//    #1 PHP Basics $30

// your code here
?>

🎉 Lesson Complete!

• ✅ A list endpoint reads the query string to filter, sort, and paginate — never dump every row
• ✅ Return meta (page, total, total_pages) and links so clients navigate without doing maths
• ✅ ?fields= (allow-listed) shrinks payloads and stops you leaking private columns
• ✅ Build dynamic WHERE from an allow-list of SQL + bound params — user input is never pasted into SQL
• ✅ Column names and sort direction can't be bound, so they must go through an allow-list too
• ✅ Version in the URL and wrap every response in one envelope for success and errors

Practice quiz

Continue this course

• Previous: Caching Techniques: OPcache, Redis & File Caching
• Next: Rate Limiting, Throttling & IP-Based Controls — Protect your API from abuse with rate limiting and throttle middleware
• Quick reference: PHP cheat sheet

Frequently asked questions

How do I stop SQL injection when filters are dynamic?

Never concatenate user input into the SQL string. Keep two allow-lists: one maps a query-param name to a fixed SQL fragment you wrote yourself (so the column names and operators are always yours), and another lists the columns you allow ORDER BY on. The user-supplied values then go into bound placeholders (:min_price) that PDO escapes when you call $stmt->execute($params). Because the SQL text never contains the raw input, a value like "'; DROP TABLE products; --" is treated as plain data, not code.

What is the difference between offset and cursor pagination?

Offset pagination uses LIMIT/OFFSET (?page=2&per_page=20) — simple, lets you jump to any page, but it gets slow on large tables because the database must count and skip every earlier row, and rows can shift if data changes between requests. Cursor pagination passes a pointer to the last item you saw (?after=12345) and asks for the next N after it — it stays fast at any depth and is stable under inserts, but you can only go forwards/backwards, not jump to page 500. Use offset for small admin lists and cursor for big public feeds.

Why wrap every response in the same envelope?

A consistent envelope — success, data, errors, meta — means a client writes one parser and reuses it for every endpoint. It can always check response.success, read response.data on success, or loop response.errors on failure, without special-casing each route. The alternative (one endpoint returns {error: '...'}, another {message: '...'}, another a bare array) forces clients to learn each endpoint's quirks and is the single most common complaint about home-grown APIs.

How should I let clients request only some fields?

Accept a ?fields=id,name parameter, split it on commas, then intersect it with an allow-list of public columns so you only ever return fields you've explicitly approved. This both shrinks the payload (great for mobile clients) and protects you from accidentally leaking sensitive columns like password_hash or is_admin — if a column isn't on the public allow-list, no value of ?fields can expose it.

When should I create a new API version?

Version when you make a breaking change — renaming or removing a field, changing a field's type, or altering required inputs. Adding a new optional field or a new endpoint is backward-compatible and does not need a new version. The most common approach is URL path versioning (/api/v1/..., /api/v2/...) because it is obvious, cache-friendly, and easy to route; keep the old version running until clients have migrated.

Related lessons

• Databases with PDO
• JSON & APIs