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.

Learn Python, JavaScript, Java and more with free interactive lessons, real projects and AI-powered help. Beginner-friendly.

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.

📋 Quick Reference — Dynamic APIs

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.

Practice quiz

In what order should a list endpoint process the result set?

Paginate, then filter, then sort
Sort, then paginate, then filter
Filter, then sort, then paginate
The order doesn't matter

Answer: Filter, then sort, then paginate. Filter -> sort -> paginate: you sort the filtered set, then slice the sorted set, so page 1 is genuinely the top rows.

With 47 items at 10 per page, how many total pages are there?

Answer: 5. ceil(47 / 10) = 5. The offset for page 3 is (3-1)*10 = 20, and there is a next page.

How do you safely build a dynamic WHERE clause from user filters?

Concatenate $_GET values straight into the SQL string
Escape the input with addslashes()
Reject any request with a quote in it
Column names/operators from an allow-list you wrote; values into bound :placeholders

Answer: Column names/operators from an allow-list you wrote; values into bound :placeholders. The SQL text comes only from an allow-list; the values go into bound placeholders PDO escapes — so even a hostile value is treated as data.

Why must you clamp per_page (e.g. min(per_page, 100))?

To make pagination links prettier
So a client can't request a million rows and exhaust memory
Because PHP arrays can't exceed 100 items
To enable caching

Answer: So a client can't request a million rows and exhaust memory. Always clamp per_page (e.g. 1..100) so nobody overrides the cap with an unbounded result set.

Which two things can you NOT bind as parameters, requiring an allow-list?

Column names and sort direction (ASC/DESC)
Integers and floats
Strings and dates
LIMIT and OFFSET values

Answer: Column names and sort direction (ASC/DESC). You can't bind a column name or sort direction, so those must always pass through an allow-list, never straight from $_GET.

How should field selection (?fields=id,name) be implemented safely?

Return whatever fields the client names
Blacklist password_hash only
Intersect the requested fields with an allow-list of public columns
Return all columns and let the client filter

Answer: Intersect the requested fields with an allow-list of public columns. Intersecting with a public-field allow-list shrinks payloads AND guarantees a private column like password_hash can never be requested.

What is the benefit of wrapping every response in one envelope (success/data/errors/meta)?

It makes responses smaller
Clients write one parser and reuse it for every endpoint
It encrypts the data
It removes the need for HTTP status codes

Answer: Clients write one parser and reuse it for every endpoint. A consistent envelope means a client always checks success, reads data, or loops errors — no per-endpoint special-casing.

What is the main trade-off of cursor pagination vs offset pagination?

Cursor is always slower
Cursor can't be used with SQL
Cursor requires no LIMIT
Cursor stays fast at any depth but can't jump to an arbitrary page

Answer: Cursor stays fast at any depth but can't jump to an arbitrary page. Offset (LIMIT/OFFSET) lets you jump to any page but slows on big tables; cursor (?after=ID) stays fast and stable but only goes forward/back.

When should you create a new API version?

Every time you add a new optional field
On a breaking change — renaming/removing a field or changing its type
Once a month on a schedule
Whenever traffic increases

Answer: On a breaking change — renaming/removing a field or changing its type. Adding an optional field or endpoint is backward-compatible; version (commonly in the URL path) only for breaking changes.

Which comparison operator does the lesson use to sort by a field?

The equality operator ==
The concatenation operator .
The spaceship operator <=>
The null-coalescing operator ??

Answer: The spaceship operator <=>. usort with $a[$field] <=> $b[$field] (or reversed for desc) orders the rows; <=> returns -1, 0, or 1.