PHP mysqli: Prepared Statements, get_result

PHP has two main ways to talk to MySQL: PDO (this chapter so far) and mysqli. Both are good, and both support prepared statements. You will meet mysqli in older code and in WordPress’s wpdb internals, so it helps to read it.

PDO or mysqli

	PDO	mysqli
Databases	MySQL, PostgreSQL, SQLite, SQL Server and more	MySQL and MariaDB only
API styles	Object-oriented	Object-oriented and procedural
Named placeholders (`:name`)	Yes	No, `?` only
MySQL-specific features	Some via attributes	More: multiple statements, async queries, `mysqli_stmt` metadata, reconnect control
Fetching as an array of rows	`fetchAll()`	`fetch_all(MYSQLI_ASSOC)`

If you are building a new application on MySQL, either works. PDO is the usual recommendation, because the same code style works with other databases and named placeholders read better. Pick mysqli when the codebase already uses it.

Connecting, with exceptions turned on

By default, mysqli reports errors as warnings. Turn on exceptions first:

index.php

PHP

<?php
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);          // throw mysqli_sql_exception on errors

$mysqli = new mysqli('127.0.0.1', 'root', '', 'phpl', (int) getenv('DB_PORT'));
$mysqli->set_charset('utf8mb4');                                    // the same as charset=utf8mb4 in a PDO DSN

echo $mysqli->server_info !== '' ? "connected\n" : "not connected\n";
echo $mysqli->character_set_name(), "\n";

Output

OUTPUT

1
2

connected
utf8mb4

(The example connects as the test server’s root to keep it short. Real code reads the credentials of a limited account from the environment, as in Connecting.) Do not forget set_charset(), or the connection may use a different character set.

A prepared SELECT

index.php

PHP

<?php
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = new mysqli('127.0.0.1', 'root', '', 'phpl', (int) getenv('DB_PORT'));
$mysqli->set_charset('utf8mb4');

$category = 'writing';
$maxPrice = 50;

$stmt = $mysqli->prepare('SELECT id, name, price FROM products WHERE category = ? AND price <= ? ORDER BY price');
$stmt->bind_param('sd', $category, $maxPrice);                       // s = string, d = double (decimal as float)
$stmt->execute();
$result = $stmt->get_result();                                       // needs the mysqlnd driver (default in PHP)

while ($row = $result->fetch_assoc()) {
    echo $row['name'], ' ', $row['price'], "\n";
}
$stmt->close();

Output

OUTPUT

1
2
3

Pencil 5.00
Pen 12.50
Marker 45.00

bind_param() takes a type string and the variables by reference: s for string, i for integer, d for double, b for blob. The variables are read when execute() runs.

Insert and the new id

index.php

PHP

<?php
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = new mysqli('127.0.0.1', 'root', '', 'phpl', (int) getenv('DB_PORT'));
$mysqli->set_charset('utf8mb4');

$name = 'Ben';
$email = 'ben@example.com';
$stmt = $mysqli->prepare('INSERT INTO customers (name, email) VALUES (?, ?)');
$stmt->bind_param('ss', $name, $email);
$stmt->execute();
echo 'insert id: ', $stmt->insert_id, ', affected rows: ', $stmt->affected_rows, "\n";

try {
    $stmt->execute();                                                 // the same email again
} catch (mysqli_sql_exception $e) {
    echo 'error ', $e->getCode(), ': duplicate email', "\n";           // getCode() is the MySQL error number here
}

Output

OUTPUT

1
2

insert id: 4, affected rows: 1
error 1062: duplicate email

With mysqli, getCode() is the MySQL error number (1062), while PDO’s getCode() is the SQLSTATE. Remember the difference when you compare codes.

Transactions

index.php

PHP

<?php
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = new mysqli('127.0.0.1', 'root', '', 'phpl', (int) getenv('DB_PORT'));

$mysqli->begin_transaction();
try {
    $mysqli->query("UPDATE products SET stock = stock - 1 WHERE id = 1");
    $mysqli->query("INSERT INTO orders (customer_id, total) VALUES (1, 80)");
    $mysqli->commit();
    echo "committed\n";
} catch (Throwable $e) {
    $mysqli->rollback();
    throw $e;
}
echo $mysqli->query('SELECT COUNT(*) FROM orders')->fetch_row()[0], " orders\n";

Output

OUTPUT

1
2

committed
4 orders

The same rules as in Transactions in PHP apply: keep them short, retry deadlocks, and keep DDL outside.

Things to avoid

mysqli_real_escape_string() for building queries. Use prepared statements instead (SQL Injection).
The old **mysql_*** functions: they were removed in PHP 7.
multi_query() with user input: it allows several statements in one call, which makes injection far worse.

What WordPress does

WordPress uses mysqli internally (through wpdb). In plugins and themes, use $wpdb->prepare() with %s, %d and %f placeholders, never string concatenation:

index.php

PHP

<?php
global $wpdb;
$rows = $wpdb->get_results($wpdb->prepare(
    "SELECT ID, post_title FROM {$wpdb->posts} WHERE post_type = %s AND post_status = %s LIMIT %d",
    'tutorial', 'publish', 10
));

lightbulb

Pick one

Mixing PDO and mysqli in a project doubles the places where configuration, charset and error handling can differ. Standardise on one for new code.