Dynamic Apis

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 examples you run in your browser, plus practice exercises and a quick quiz.

What You'll Learn in This Lesson

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.

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.

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() .

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.

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.

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.

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

Feature

Query string

Safe implementation

Pagination

?page=2&per_page=20

Bound LIMIT :limit OFFSET :offset , clamp per_page

Filtering

?category=books

Allow-list → col = :val bound param

Sorting

?sort=price:desc

Allow-list column; map dir to ASC/DESC

Fields

?fields=id,name

array_intersect with a public-field list

Search

?q=php

LIKE :q bound as %php%

Versioning

/api/v2/resource

URL path; keep v1 alive until clients migrate

Envelope

—

{ success, data, errors, meta } everywhere

Frequently Asked Questions

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.

🎉 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
• ✅ Next lesson: Rate Limiting — protect these endpoints from abuse by throttling requests

Practice quiz

Continue this course

• Previous: Caching Techniques
• Next: Rate Limiting

Related lessons

• Databases with PDO
• JSON & APIs