skip to content

With PHP's mysqli prepared statements, what does the type string passed to bind_param() mean, and why must its arguments be variables?

level: middleimportance: should knowfreq 42%

answer

  1. one letter per placeholder
  2. i, d, s, b
  3. bound by reference, read at execute()
  4. no literals as arguments
  5. get_result() for a mysqli_result

basics

~20 s

Each letter of bind_param()'s type string gives one placeholder's type: i integer, d double, s string, b blob. The values are bound by reference and read when execute() runs, so only variables can be passed.

solid answer

~40 s

`bind_param('is', $classId, $weekday)` has one letter per `?` marker: `i` integer, `d` double, `s` string, `b` blob sent with `send_long_data()`. A letter count that differs from the variables throws `ArgumentCountError`, and an unknown letter throws `ValueError`. The parameters are declared `mixed &...$vars`, so mysqli stores references and reads the variables each time `execute()` runs; that is why literals like `5` cannot be passed, and why I can bind once and loop, changing the variables before each `execute()`. The letter shapes the value sent: a float bound with `i` is truncated. For rows I call `get_result()`, always available since PHP 8.2 made mysqlnd mandatory. PHP 8.1's `execute([...])` and 8.2's `execute_query()` are shorter but bind everything as strings.

code

php · 11 lines
php
<?php
declare(strict_types=1);

$stmt = $db->prepare(
    'INSERT INTO lessons (class_id, weekday, period, room) VALUES (?, ?, ?, ?)'
);
$stmt->bind_param('isis', $classId, $weekday, $period, $room);

foreach ($plan as [$classId, $weekday, $period, $room]) {
    $stmt->execute(); // reads the four variables as they are now
}

go deeper

for a junior

Recall the four type letters i, d, s and b, and that bind_param() needs one letter per ? marker.

for a middle

Explain binding by reference: values are read at execute() time, so literals fail and one binding can serve a loop.

for a senior

Spot the subtle bugs: floats truncated by i, variables reassigned before execute(), and array-bound values all sent as strings.

for a principal

Decide whether a legacy codebase standardises on execute_query() for brevity or keeps typed bind_param() where exact types matter.

## The prepared-statement flow in mysqli A mysqli prepared statement is a `mysqli_stmt` object. The classic sequence is four calls: 1. `$stmt = $db->prepare('SELECT ... WHERE class_id = ? AND weekday = ?')` sends the SQL with `?` markers to the server. 2. `$stmt->bind_param('is', $classId, $weekday)` attaches PHP variables to the markers. 3. `$stmt->execute()` sends the current values and runs the statement. 4. `$stmt->get_result()` returns a `mysqli_result` to fetch rows from, for statements that produce rows. ## The type string The first argument of `bind_param(string $types, mixed &...$vars): bool` has **one letter per placeholder**, in order: | Letter | Sent as | Typical use | |---|---|---| | `i` | integer | ids, counts, flags | | `d` | double (float) | measurements, ratios | | `s` | string | text, dates, decimals you want exact | | `b` | blob, sent in packets | large binary data, with `send_long_data()` | Mistakes are caught early: - A letter count that differs from the number of variables throws `ArgumentCountError`: "The number of elements in the type definition string must match the number of bind variables". - A variable count that differs from the placeholders in the statement throws `ArgumentCountError` as well. - Any other letter throws `ValueError`, because the string may only contain `b`, `d`, `i` and `s`. The letter affects how values travel. A PHP float bound with `i` is converted to an integer, so `2.5` becomes `2`. A PHP `null` is sent as SQL `NULL` whatever its letter. With `b`, the data is sent separately with `send_long_data()` before `execute()`. ## Why the arguments are variables `bind_param()` takes its values **by reference** (`mixed &...$vars`). It does not copy the values when you call it; it remembers the variables and reads them each time `execute()` runs. Two consequences: - You cannot pass literals: `bind_param('i', 5)` throws an `Error` saying the argument could not be passed by reference. A function result such as `trim($x)` is accepted with an `E_NOTICE` ("Only variables should be passed by reference"), which binds a temporary nobody can change afterwards. - You can bind once and execute many times, changing the variables in between. Inserting a week of lessons becomes one `prepare()`, one `bind_param()`, and a loop that assigns the variables and calls `execute()`. ## Getting rows back | Method | Returns | Style | |---|---|---| | `get_result()` | `mysqli_result`, then `fetch_assoc()`, `fetch_all(MYSQLI_ASSOC)`, or `foreach` | rows as arrays, like `query()` | | `bind_result()` + `fetch()` | fills bound PHP variables one row at a time | column-by-column, older code | `get_result()` depends on the mysqlnd driver library. Since PHP 8.2 mysqli can only be built with mysqlnd, so the method is always available; older code sometimes avoided it because some builds lacked it. ## Shorter forms in current PHP - **PHP 8.1:** `$stmt->execute([$classId, $weekday])` binds a **list array** directly, with every value sent as a string. - **PHP 8.2:** `$db->execute_query($sql, [$classId, $weekday])` prepares, binds, executes and returns the result in one call, also binding values as strings. Explicit `bind_param()` remains the way to choose types per value and to reuse one statement in a loop. ## Procedural spellings Legacy code often uses the procedural names, which map one to one onto the methods: | Method | Procedural function | |---|---| | `$db->prepare($sql)` | `mysqli_prepare($db, $sql)` | | `$stmt->bind_param($types, ...)` | `mysqli_stmt_bind_param($stmt, $types, ...)` | | `$stmt->execute()` | `mysqli_stmt_execute($stmt)` | | `$stmt->get_result()` | `mysqli_stmt_get_result($stmt)` | The type string and the by-reference rule are identical in both spellings. ## Common bugs in legacy code - Binding everything as `s` without thinking — usually harmless, because the server converts, but it hides the intended type from the next reader. - Binding a value and then reassigning the variable before `execute()` without realising the new value is the one sent. - Calling `bind_param()` inside the loop on every iteration: it works, but defeats the point of binding once. - Mixing up `bind_param()` (inputs) and `bind_result()` (outputs).

  • What happens with bind_param('i', 5) and with bind_param('i', intval($id))?
    `bind_param()` declares its values as references. A literal such as `5` has no variable behind it, so the call throws an `Error` saying the argument could not be passed by reference. A function result like `intval($id)` gets through with an `E_NOTICE` "Only variables should be passed by reference" and binds a temporary. Assign to a variable first in both cases.
  • When would you still use bind_result() instead of get_result()?
    Rarely in new code. `bind_result()` binds output columns to variables and fills them on each `fetch()`, which avoids building an array per row. It was once needed on builds without mysqlnd; since PHP 8.2 `get_result()` always exists and is the simpler choice.

saying these in an interview costs you the question

  • Thinking the type string names one type for all parameters.
  • Passing literals straight into bind_param() as if it took values.
  • Believing bind_param() copies values at the moment it is called.
  • Using 'f' for floats instead of 'd'.
  • Expecting execute([...]) in PHP 8.1 to infer int types.