For a PHP product search, how do you bind a variable-length IN list and LIMIT/OFFSET pagination with PDO placeholders?
answer
- one ? per element
- empty list: IN () is invalid SQL
- array_values() before execute()
- LIMIT and OFFSET with PARAM_INT
- clamp page size, derive the offset
basics
~20 sGenerate one ? per IN-list element, skip the predicate when the list is empty, and re-index values with array_values(). Bind LIMIT and OFFSET with bindValue(..., PDO::PARAM_INT) after clamping the page size and deriving the offset.
solid answer
~40 sA marker holds one value, so an `IN` list needs **one `?` per element**: `implode(',', array_fill(0, count($ids), '?'))` builds the placeholders, and `array_values($ids)` guarantees the 0-based keys `execute()` expects for `?`. An empty list must **drop the predicate** (or short-circuit to no results), because `IN ()` is not valid SQL. For pagination, clamp the page size to an allowed range, compute `$offset = ($page - 1) * $perPage` with `$page >= 1`, and bind both with `bindValue(..., PDO::PARAM_INT)`: under MySQL's default emulated prepares a string-bound `LIMIT ?` becomes `LIMIT '20'`, which the server rejects. Because an array passed to `execute()` discards earlier `bindValue()` calls, bind everything with `bindValue()` in that case and call `execute()` with no argument.
code
php · 21 lines<?php
declare(strict_types=1);
/** @var PDO $pdo */
$ids = array_values(array_unique(array_filter(
array_map('intval', (array) ($_GET['category'] ?? [])),
fn (int $id): bool => $id > 0,
)));
$perPage = min(100, max(1, (int) ($_GET['per_page'] ?? 20)));
$page = max(1, (int) ($_GET['page'] ?? 1));
$where = $ids === [] ? '' : 'WHERE category_id IN (' . implode(',', array_fill(0, count($ids), '?')) . ')';
$stmt = $pdo->prepare("SELECT id, name, price FROM products $where ORDER BY id LIMIT ? OFFSET ?");
$pos = 1;
foreach ($ids as $id) {
$stmt->bindValue($pos++, $id, PDO::PARAM_INT);
}
$stmt->bindValue($pos++, $perPage, PDO::PARAM_INT);
$stmt->bindValue($pos, ($page - 1) * $perPage, PDO::PARAM_INT);
$stmt->execute();go deeper
Remember that each IN value needs its own ? and that LIMIT needs an integer, not a quoted string.
Build the marker list, handle the empty list, re-index with array_values(), and bind LIMIT/OFFSET with PDO::PARAM_INT.
Avoid the execute(array) trap that drops typed binds, clamp paging input, and know when long IN lists or deep offsets need a different query shape.
Provide one small query-building helper for lists and paging so every endpoint gets these rules right without re-deriving them.
## The request A product-search endpoint accepts `category[]=3&category[]=7&category[]=12`, a `page` and a `per_page`. The SQL needs `category_id IN (...)` with a variable number of values, plus `LIMIT` and `OFFSET`. Neither fits a single placeholder. ## The IN list: one marker per value The PDO manual is explicit: a marker represents one complete data literal, and you cannot bind several values to a single marker in an `IN()` clause. So the SQL must contain as many markers as there are values: 1. **Normalise the input.** Cast each id to `int`, drop invalid ones, remove duplicates. 2. **Handle the empty case.** `IN ()` is a syntax error. Either leave the predicate out (no category filter) or return an empty result without querying, whichever the product requires. 3. **Build the markers.** `implode(',', array_fill(0, count($ids), '?'))` produces `?,?,?`. 4. **Re-index.** `array_filter()` and `array_unique()` keep the original keys, so `[0 => 3, 2 => 7]` has a gap. For `?` markers, `execute()` maps array index 0 to the first marker, 1 to the second, and so on; a gap leaves a marker without a value and the call fails, typically with `HY093`. Call `array_values()` before binding. With named markers, generate names instead: `:cat0, :cat1, ...`, and build a matching keyed array. That is useful when the rest of the query already uses named markers, because the two styles cannot be mixed. ## LIMIT and OFFSET: integers, typed Values passed in an `execute()` array are bound as `PDO::PARAM_STR`. Under emulated prepares, the default for `pdo_mysql`, a string value is quoted into the SQL, so `LIMIT ?` becomes `LIMIT '20'` and the server rejects it. Two fixes, best used together: - Bind with **`bindValue($pos, $perPage, PDO::PARAM_INT)`**, which writes an unquoted integer under emulation and sends an integer parameter natively. - Turn emulation off for the connection, so values travel as parameters. Before binding, make the numbers safe for the query itself: - **Clamp** the page size to an allowed range, for example 1 to 100, so a client cannot ask for a million rows. - **Floor** the page at 1 and compute `$offset = ($page - 1) * $perPage`. ## Do not mix execute(array) with earlier binds Passing an array to `execute()` clears every binding made earlier with `bindValue()` or `bindParam()` and binds only the array's contents, all as strings. A common broken version binds `LIMIT` with `PARAM_INT` and then calls `execute($ids)`: the `LIMIT` binding is gone and the call fails, typically with `HY093`. Bind every value, the ids included, with `bindValue()`, then call `execute()` with no argument. ## Named-marker variant When the rest of the product query uses named markers, generate names for the list: - build `$names = array_map(fn (int $i): string => ':cat' . $i, array_keys($ids))` after `array_values()`; - put `implode(',', $names)` into the `IN (...)`; - bind each with `bindValue($names[$i], $ids[$i], PDO::PARAM_INT)`. Generated names avoid position arithmetic, and a name that is bound twice or not at all shows up as a clear parameter error. ## Large lists and offsets - Very long `IN` lists create long statements with many parameters, and databases cap the number of placeholders per statement. For thousands of ids, use a temporary table or a join instead; that is a database design choice rather than a PDO one. - Deep `OFFSET` pagination makes the database read and discard all earlier rows. Keyset pagination (`WHERE id > ? ORDER BY id LIMIT ?`) avoids that, and binds the same way. ## Summary | Need | PDO technique | |---|---| | variable `IN` list | one generated `?` per value, `array_values()`, empty list handled before building SQL | | `LIMIT` / `OFFSET` | clamped ints bound with `PDO::PARAM_INT` | | mixing with other filters | append values to one positional list in SQL order, or use generated named markers | | typed binds plus a list | `bindValue()` for everything, then `execute()` with no argument |
- Why not implode the integer ids straight into the SQL after casting them with intval()?After a strict `(int)` cast the string cannot carry SQL, so it is not an injection hole by itself. But it creates a different SQL text for every combination, mixes two styles of value handling in one codebase, and depends on nobody removing the cast later. Generated markers keep every value on the binding path.
- How do you add a name filter with LIKE to the same positional query?Append its marker and value in SQL order: the `LIKE ?` marker goes into the SQL where the condition sits, and its bound value, the escaped pattern wrapped in `%`, is bound at the matching position. Keeping one ordered list of values next to the SQL fragments avoids off-by-one mistakes.
saying these in an interview costs you the question
- One ? can take the whole array of ids
- IN () with an empty list simply matches nothing
- execute([...]) binds LIMIT values as integers automatically
- Any array can be passed to execute() for ? markers, whatever its keys
- A client-supplied per_page can go straight into LIMIT