skip to content

In PHP 8.2 and later, what does mysqli::execute_query() replace, and how does it bind the values you pass it?

level: middleimportance: nice to knowfreq 22%

answer

  1. prepare, bind, execute, get_result in one
  2. a list array, not keys
  3. every value sent as a string
  4. mysqli_result for SELECT, true otherwise
  5. not the deprecated mysqli_execute()

basics

~10 s

mysqli::execute_query() (PHP 8.2) does prepare(), bind_param(), execute() and get_result() in one call. Values come as a list array matching the ? markers, and each is bound as a string.

solid answer

~40 s

`$db->execute_query($sql, [$classId, $weekday])` prepares the statement, binds the array to the `?` markers, executes it and returns a `mysqli_result` for a `SELECT` or `true` for other statements. The array must be a **list** with exactly one element per marker, otherwise it throws `ValueError`, and every value is bound as a **string**, with `null` sent as SQL `NULL`. Because the statement object is hidden, `affected_rows` and `error` are read from the connection. I use it to modernise legacy `mysqli_query()` calls that interpolate variables. I keep `prepare()` with `bind_param()` when I need explicit types, blobs, or one statement executed many times in a loop.

code

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

// Before: $res = mysqli_query($link, "SELECT * FROM lessons WHERE room = '$room'");
$result = $db->execute_query(
    'SELECT subject, period FROM lessons WHERE room = ? AND weekday = ?',
    [$room, $weekday]
);
foreach ($result as $row) {
    echo $row['period'], ': ', $row['subject'], PHP_EOL;
}

$db->execute_query('UPDATE lessons SET room = ? WHERE id = ?', [$newRoom, $lessonId]);
echo $db->affected_rows; // status copied to the connection

go deeper

for a junior

Recall that execute_query() runs a parameterised query in one call and takes its values as an array.

for a middle

Explain the binding rules: list array, exact count, every value as a string, and where status is read afterwards.

for a senior

Choose between execute_query() and an explicit prepared statement by weighing types, blobs and repeated execution in loops.

for a principal

Plan a legacy migration where execute_query() lets each interpolated query become parameterised in a small, reviewable change.

## What it replaces Before PHP 8.2, a parameterised query in mysqli took four calls and three objects: ```php $stmt = $db->prepare('SELECT subject, room FROM lessons WHERE class_id = ? AND weekday = ?'); $stmt->bind_param('is', $classId, $weekday); $stmt->execute(); $result = $stmt->get_result(); ``` **PHP 8.2** added `mysqli::execute_query(string $query, ?array $params = null): mysqli_result|bool` and its procedural twin `mysqli_execute_query($mysql, $query, $params)`. The manual calls it a shortcut for `prepare()`, `bind_param()`, `execute()` and `get_result()`: ```php $result = $db->execute_query( 'SELECT subject, room FROM lessons WHERE class_id = ? AND weekday = ?', [$classId, $weekday] ); ``` ## How the values are bound - `$params` must be a **list array** — keys `0, 1, 2, ...` in order. An associative array throws `ValueError` ("must be a list array"); mysqli has no named placeholders to match keys against. - It must hold **exactly** as many elements as there are `?` markers, or a `ValueError` reports the expected and actual counts. - **Every value is bound as a string.** There is no type string. The server converts the strings where the column or expression needs a number, which is what makes the shortcut work for ordinary lookups and inserts. - A PHP `null` is still sent as SQL `NULL`. PHP 8.1 had already added the same list-array binding to `mysqli_stmt::execute(?array $params = null)`, with the same all-strings rule; `execute_query()` goes further by hiding the statement object entirely. ## What it returns | Statement | Return value | |---|---| | `SELECT`, `SHOW`, `DESCRIBE`, `EXPLAIN` | a `mysqli_result` | | other successful statements (`INSERT`, `UPDATE`, ...) | `true` | | failure | `false` — but with the default report mode it throws `mysqli_sql_exception` instead | Because the internal statement object is never exposed, its status is copied to the `mysqli` connection: read `$db->affected_rows` or `$db->error` there after an `INSERT` or `UPDATE`. ## When the shortcut is the wrong tool 1. **Types matter.** Binding everything as strings is fine for most comparisons, but when you want a float sent as a float, or explicit control over how a value is interpreted, use `prepare()` and `bind_param()` with a type string. 2. **Repeated execution.** `execute_query()` prepares a new statement on every call. Inserting several hundred timetable rows is better done with one `prepare()` and a loop of `execute()` calls. 3. **Blobs.** Streaming large data with `send_long_data()` needs a statement object. 4. **Several statements.** The query must be a single SQL statement; `execute_query()` is not a replacement for `multi_query()`. ## Errors it can raise Two kinds of failure come out of `execute_query()`, and they behave differently: - **Programming errors in the call** — an associative array, or the wrong number of values — throw `ValueError` straight away, whatever the report mode. They are bugs to fix, not conditions to handle. - **Database errors** — a syntax error in the SQL, a duplicate key, a lost connection — follow the report mode. With PHP 8.1+'s default they throw `mysqli_sql_exception`; with reporting turned off the method returns `false` and the details are on `$db->error` and `$db->errno`. ## Not to be confused with - `mysqli_execute()` — an old alias of `mysqli_stmt_execute()`, formally **deprecated in PHP 8.5**. It executes an already-prepared statement and has nothing to do with `execute_query()`. - `mysqli::query()` — runs SQL with **no** parameters at all. Interpolating values into its string is exactly what `execute_query()` exists to replace. ## Where it fits in a legacy codebase A legacy timetable app full of `mysqli_query($link, "... WHERE class_id = $classId")` calls can be migrated one call at a time: replace the interpolated values with `?`, move them into the array, and switch to `execute_query()`. Each change is small, and the result is a parameterised query with the same return type the old code already handles: a `mysqli_result` it can `fetch_assoc()` from.

  • Why does execute_query() reject ['class' => 7, 'day' => 'Mon']?
    mysqli has only positional `?` markers, so there are no names to match keys against. The method requires a list array with keys `0, 1, 2, ...` in order and throws `ValueError` ("must be a list array") for anything else. Pass `array_values()` of the data, in marker order.
  • Is execute_query() a good choice for inserting 500 lessons in a loop?
    Not ideal. Each call prepares a new statement, so the server parses the same SQL 500 times. One `prepare()`, one `bind_param()` and a loop of `execute()` calls reuse the prepared statement; wrapping the loop in a transaction also avoids 500 separate commits.

saying these in an interview costs you the question

  • Passing an associative array to execute_query() as if it had named placeholders.
  • Expecting execute_query() to infer integer and float types.
  • Confusing execute_query() with the deprecated mysqli_execute() alias.
  • Looking for affected rows on a statement object execute_query() never returns.
  • Believing execute_query() can run several semicolon-separated statements.