Skip to content
Tutorials Center
My learning

Topics

Lesson 92 of 147 ยท Databases

Using MySQLi

Learn the PHP MySQLi extension - connecting, queries, prepared statements with bind_param and execute_query, errors, transactions, and how it compares to PDO.

MySQLi ("MySQL improved") is PHP's other way to talk to a database. It replaced the ancient mysql_* functions back in PHP 5, and unlike PDO it works with only one family of databases: MySQL and MariaDB. In return it exposes a few MySQL-specific features, and it's what millions of older tutorials, WordPress plugins and in-house applications are written with.

This course uses PDO everywhere else, and for new code that's the better default (the PHP and Databases lesson explains why). But you will meet MySQLi in real projects, so you need to read it fluently, spot the dangerous patterns, and know how each piece maps to PDO. That's the goal of this lesson.

Our online editor has no MySQL server and its PHP build doesn't include the mysqli extension, so the MySQLi code in this lesson is read-only. The runnable examples show the same ideas with PDO and an in-memory SQLite database, which is how you'd translate MySQLi code anyway.

You can check what your own PHP has with a couple of function calls:

PHP Which database extensions are loaded?

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with fgets(STDIN)), it gets the next line from this box.

On our server this prints no for mysqli and lists mysql, pgsql, sqlite as PDO drivers. mysqlnd (the MySQL native driver) is the low-level library that both mysqli and pdo_mysql use to speak MySQL's network protocol. On a typical Linux server you install MySQLi with a package such as php8.5-mysql, which brings both extensions.

Connecting

MySQLi has two equivalent styles: an object-oriented API and a procedural one where every method becomes a function that takes the connection as its first argument. Modern code uses the object style:

PHP
<?php
// Object-oriented style (use this)
$db = new mysqli('127.0.0.1', 'app_user', 'secret', 'shop', 3306);
$db->set_charset('utf8mb4');

// The same in procedural style, common in older code
$link = mysqli_connect('127.0.0.1', 'app_user', 'secret', 'shop', 3306);
mysqli_set_charset($link, 'utf8mb4');

Always call set_charset('utf8mb4'). MySQL's old utf8 is a three-byte subset that can't store emoji, and setting the charset through this method (not with a SET NAMES query) also tells MySQLi which encoding to assume when it escapes strings.

The PDO equivalent puts everything into a DSN string, as you saw in PDO Basics:

PHP
$pdo = new PDO('mysql:host=127.0.0.1;port=3306;dbname=shop;charset=utf8mb4', 'app_user', 'secret');

Errors throw exceptions (since PHP 8.1)

For most of its history, MySQLi reported failures by returning false and expected you to check $db->error after every call. Code full of or die(mysqli_error($link)) comes from that era. Since PHP 8.1 the default is mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT), which makes every failure throw a mysqli_sql_exception:

PHP
try {
    $db = new mysqli('127.0.0.1', 'app_user', 'wrong-password', 'shop');
} catch (mysqli_sql_exception $e) {
    error_log('Database connection failed: ' . $e->getMessage());
    http_response_code(500);
    exit('Sorry, something went wrong.');
}

Never show $e->getMessage() to visitors. It can contain the host name and user name, which helps attackers. Log it and show a generic message. If you maintain code that turns reporting off with mysqli_report(MYSQLI_REPORT_OFF), every call needs its own false check, and missing checks are a common source of silent bugs.

Running simple queries

query() sends SQL and returns a mysqli_result for statements that return rows (or true for others). The result object can be looped over with foreach or read with its fetch methods:

PHP
$result = $db->query('SELECT id, name, price FROM products ORDER BY name');

echo $result->num_rows, " products\n";

foreach ($result as $row) {              // each $row is an associative array
    echo $row['name'], ': ', $row['price'], "\n";
}

$all = $db->query('SELECT id, name FROM products')->fetch_all(MYSQLI_ASSOC);
$one = $db->query('SELECT COUNT(*) FROM products')->fetch_column(); // PHP 8.1+
MySQLi PDO equivalent
$result->fetch_assoc() $stmt->fetch(PDO::FETCH_ASSOC)
$result->fetch_all(MYSQLI_ASSOC) $stmt->fetchAll(PDO::FETCH_ASSOC)
$result->fetch_object() $stmt->fetch(PDO::FETCH_OBJ)
$result->fetch_column() (8.1) $stmt->fetchColumn()
$result->num_rows count($stmt->fetchAll()) (no reliable equivalent)
$db->insert_id $pdo->lastInsertId()
$db->affected_rows $stmt->rowCount()

One quirk: by default MySQLi returns every value as a string, even INT columns, unless you use prepared statements or turn on MYSQLI_OPT_INT_AND_FLOAT_NATIVE. So $row['id'] === 5 is false in a lot of MySQLi code; cast with (int) when it matters.

Never build SQL with escaping

Old MySQLi code often "protects" input with real_escape_string():

PHP
// Old and fragile: do not write new code like this
$name = $db->real_escape_string($_GET['name']);
$result = $db->query("SELECT * FROM customers WHERE name = '$name'");

Escaping backslash-protects quotes and a few control characters, and inside a quoted string with the right charset it does work. The trouble is that it only protects strings in quotes, and it's easy to forget once among hundreds of queries. A numeric value without quotes isn't protected at all. This simulation uses addslashes(), which escapes the same quote characters, against SQLite:

PHP Why escaping is not enough

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with fgets(STDIN)), it gets the next line from this box.

The escaped query returns all three orders, while the prepared statement returns none, because no order has the ID "0 OR 1=1". The rule is the same as in Prepared Statements and SQL Injection: values go into placeholders, never into the SQL string.

Prepared statements with bind_param

MySQLi supports only positional ? placeholders; there are no named :placeholders as in PDO. The classic sequence is prepare, bind, execute, fetch:

PHP
$stmt = $db->prepare('SELECT id, name, price FROM products WHERE category = ? AND price <= ?');
$stmt->bind_param('sd', $category, $maxPrice);   // s = string, d = double

$category = 'books';
$maxPrice = 25.0;
$stmt->execute();

$result = $stmt->get_result();                   // needs mysqlnd (the default)
while ($row = $result->fetch_assoc()) {
    echo $row['name'], "\n";
}
$stmt->close();

bind_param() takes a type string with one letter per placeholder:

Letter Type Use for
i integer INT, BIGINT, booleans as 0/1
d double FLOAT, DOUBLE (and DECIMAL if you accept float precision)
s string text, dates, DECIMAL money values like '19.99'
b blob large binary data sent in packets with send_long_data()

When in doubt, s is safe: MySQL converts strings to the column type. A wrong letter is a subtle bug, though: binding '12.5' with i sends 12.

Variables are bound by reference

The detail that confuses people most: bind_param() doesn't copy the values. It binds the variables, by reference, and reads them each time you call execute(). That's why the code above can assign $category after binding. It also means you can prepare once and execute in a loop by changing the variables.

PDO's bindParam() works the same way (and its bindValue() copies the value instead), so you can try the behavior here:

PHP Binding by reference, like bind_param

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with fgets(STDIN)), it gets the next line from this box.

The MySQLi version of the same loop is $stmt->bind_param('si', $name, $priceCents); before the foreach. Because of references, bind_param() can't take literals: bind_param('i', 5) is an error. You need a variable.

Easier: execute() with an array (8.1) and execute_query() (8.2)

Two newer methods remove most of the ceremony. Since PHP 8.1, execute() accepts an array of values, all bound as strings. Since PHP 8.2, execute_query() prepares, binds and executes in one call and returns the result:

PHP
// PHP 8.1+
$stmt = $db->prepare('UPDATE products SET price = ? WHERE id = ?');
$stmt->execute(['19.99', 42]);
echo $stmt->affected_rows, " row(s) changed\n";

// PHP 8.2+: one line for a parameterized SELECT
$rows = $db->execute_query(
    'SELECT id, name FROM products WHERE category = ? ORDER BY name LIMIT ?',
    ['books', 10]
)->fetch_all(MYSQLI_ASSOC);

If you maintain MySQLi code on a current PHP version, execute_query() is the simplest way to replace string-built queries.

IN lists need dynamic placeholders

Neither MySQLi nor PDO can bind an array to a single ?. For WHERE id IN (...) you generate one placeholder per value, plus (in MySQLi) a matching type string:

PHP Placeholders and types for an IN list

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with fgets(STDIN)), it gets the next line from this box.

The output shows ?, ?, ?, the type string iii, and the matching rows Pen, Pad, Clip. In MySQLi you unpack the values with ...$ids, as covered in Destructuring and Spread. The empty-list check matters: IN () is a syntax error in MySQL.

Creating databases and tables from PHP

Most of the time the database and its tables already exist, created by a migration tool or by a script you ran once. But installers, test suites and small tools do need to create them from PHP, and this is also the best place to understand num_rows and affected_rows. Run the code below with an account that has the CREATE privilege; an application's everyday account should not have it.

PHP
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

// Connect without choosing a database, because it may not exist yet
$db = new mysqli('127.0.0.1', 'root', '', '', 3306);
$db->set_charset('utf8mb4');

$db->query('CREATE DATABASE IF NOT EXISTS shop
            CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci');
echo $db->affected_rows;      // 1 if it was created, 0 if it already existed

$db->select_db('shop');       // same as passing the name to the constructor

$db->query('CREATE TABLE IF NOT EXISTS customers (
    id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    email      VARCHAR(190) NOT NULL UNIQUE,
    name       VARCHAR(100) NOT NULL,
    created_at TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB');

$db->query('CREATE TABLE IF NOT EXISTS orders (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    customer_id INT UNSIGNED  NOT NULL,
    total       DECIMAL(10,2) NOT NULL,
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE
) ENGINE=InnoDB');

A few details deserve attention:

  • Character set. Create databases and tables as utf8mb4 so every character, including emoji, is stored correctly. utf8mb4_0900_ai_ci is the default collation in MySQL 8; on MariaDB use utf8mb4_unicode_ci.
  • IF NOT EXISTS makes the statement safe to repeat. Without it a second run fails with error 1007 (database exists) or 1050 (table exists).
  • Engine. InnoDB is required for foreign keys and transactions. The customer_id column must have exactly the same type as the referenced id, including UNSIGNED.
  • Identifiers cannot be bound. A ? placeholder stands for a value only. You cannot prepare CREATE TABLE ?, so table and database names must come from your own code (never from a request). If a name must be dynamic, validate it against /^[A-Za-z0-9_]{1,64}$/ and wrap it in backticks.
  • CREATE, DROP and ALTER make an implicit commit in MySQL, so they cannot be rolled back by a transaction.

What the counters mean

After running statements you will often want to know what happened. MySQLi has several counters, and they are easy to mix up:

Property Meaning Example
$result->num_rows rows returned by a SELECT 3 after selecting three customers
$result->field_count / $db->field_count columns in the result 4 for SELECT * on the table above
$db->affected_rows rows changed by the last INSERT, UPDATE, DELETE (or created database: 1) 2 after a DELETE that removed two rows
$db->insert_id the AUTO_INCREMENT id of the last INSERT 3 after inserting the third customer
$stmt->affected_rows the same, for one prepared statement 1 per executed insert

Two surprises: affected_rows for an UPDATE counts only rows whose value actually changed (running the same update twice gives 1, then 0, unless you connect with the MYSQLI_CLIENT_FOUND_ROWS flag), and num_rows on an unbuffered result is only correct after you have fetched every row.

Checking for errors works as always. With reporting turned on, a failed statement throws mysqli_sql_exception whose getCode() is the MySQL error number:

PHP
try {
    $db->query('CREATE TABLE customers (id INT)');
} catch (mysqli_sql_exception $e) {
    echo $e->getCode(), ' ', $e->getMessage();
    // 1050 Table 'customers' already exists
}

try {
    $stmt = $db->prepare('INSERT INTO customers (email, name) VALUES (?, ?)');
    $stmt->bind_param('ss', $email, $name);
    $stmt->execute();
} catch (mysqli_sql_exception $e) {
    if ($e->getCode() === 1062) {
        echo 'That e-mail address is already registered.';   // duplicate entry
    } else {
        throw $e;
    }
}

Changing a table later uses ALTER TABLE customers ADD COLUMN phone VARCHAR(30) NULL AFTER name and CREATE INDEX idx_name ON customers (name); DESCRIBE customers or SHOW TABLES lets a script inspect the result. In real projects keep schema changes in versioned migration files run by a tool, not in the middle of request handling (see Database Design in PHP).

MySQLi needs a running MySQL or MariaDB server and is not available in the in-browser runner, so these snippets are read-only. The same statements work unchanged with PDO, where PDO::exec() returns the number of affected rows.

Transactions and other features

MySQLi's transaction methods mirror PDO's, with slightly different spelling:

PHP
$db->begin_transaction();
try {
    $db->execute_query('UPDATE accounts SET balance = balance - ? WHERE id = ?', [3000, 1]);
    $db->execute_query('UPDATE accounts SET balance = balance + ? WHERE id = ?', [3000, 2]);
    $db->commit();
} catch (Throwable $e) {
    $db->rollback();   // lowercase "b", unlike PDO's rollBack()
    throw $e;
}

The pattern and the pitfalls are exactly those of the Transactions lesson.

A few MySQLi-only features you might see:

  • multi_query() runs several semicolon-separated statements in one call. That's handy for running a schema file, and dangerous anywhere near user input, because an injection can then add whole new statements. PDO's MySQL driver allows multiple statements in emulated mode too, but MySQLi makes it an explicit, separate method.
  • $db->ping() used to check whether a long-running worker's connection was still alive. It's deprecated as of PHP 8.4 (reconnecting this way no longer works); reconnect on error instead.
  • $db->info, $db->warning_count and get_warnings() expose MySQL's own diagnostics after a statement.
  • Asynchronous queries with MYSQLI_ASYNC and mysqli_poll() let one script wait on several queries, a niche feature.

Translating MySQLi code to PDO

When you modernize a project, the cleanest route is to hide the database behind a small class of your own, port the queries one by one, and switch the implementation underneath. Here is a minimal wrapper with the two methods most code needs; it runs on PDO, but its API looks like execute_query():

PHP A small wrapper for migrating old code

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with fgets(STDIN)), it gets the next line from this box.

The output lists Ana and Chen and then Moved 2 customers. A wrapper like this is also easy to replace in tests, an idea you'll meet in OOP Design Principles.

MySQLi or PDO?

MySQLi PDO
Databases MySQL and MariaDB only MySQL, PostgreSQL, SQLite, SQL Server, Oracle and more
Placeholders ? only ? and :name
Binding type string (bind_param) or array (8.1+) array in execute(), or bindValue/bindParam
Fetching into classes fetch_object(ClassName::class) FETCH_CLASS, FETCH_KEY_PAIR, FETCH_GROUP and more
MySQL-specific extras multi_query, async queries, detailed warnings fewer
Default errors (PHP 8.1+) exceptions exceptions (since 8.0)

Both are safe when you use prepared statements, and both perform about the same, since they share mysqlnd. Choose PDO for new code: one API for every database, named placeholders, and richer fetch modes. Keep MySQLi where a codebase already uses it consistently, but apply the modern habits: exceptions on, utf8mb4, prepared statements or execute_query() for every value.

If you ever see mysql_connect(), mysql_query() or mysql_real_escape_string() (without the "i"), that code is from the PHP 5 era. The mysql extension was removed in PHP 7.0, so it can't run on any supported PHP version. It needs a rewrite, not a patch.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. What does the type string in $stmt->bind_param('sis', $a, $b, $c) mean?

    Each letter describes one placeholder in order - s for string, i for integer, d for double, b for blob.
  2. 2. You call $stmt->bind_param('i', $id);, then set $id = 7; and call $stmt->execute();. Which value is sent?

    bind_param() binds variables by reference and reads them at execute() time, so the current value 7 is used.
  3. 3. Since PHP 8.1, what happens by default when a MySQLi query fails?

    PHP 8.1 changed the default report mode to MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT, so errors throw exceptions.
  4. 4. Why is "WHERE id = " . $db->real_escape_string($_GET['id']) still vulnerable?

    Escaping only protects values inside quoted strings. Prepared statements protect every value regardless of context.
  5. 5. Which method, added in PHP 8.2, prepares, binds and executes a MySQLi query in one call?

    $db->execute_query($sql, $params) returns a mysqli_result for SELECTs, so fetch_all() can follow directly.

Summary

  • MySQLi is the MySQL/MariaDB-only database extension; PDO works with many databases and is the better default for new code.
  • Connect with new mysqli(host, user, password, database) and always call set_charset('utf8mb4').
  • Since PHP 8.1, failures throw mysqli_sql_exception. Log the message; never show it to visitors.
  • query() returns a mysqli_result with fetch_assoc(), fetch_all(MYSQLI_ASSOC), fetch_column() and num_rows. Values usually come back as strings.
  • Prepared statements use ? only. bind_param('sid', ...) binds variables by reference with a type letter per placeholder; execute([...]) (8.1) and execute_query() (8.2) are simpler.
  • real_escape_string() is not a substitute for prepared statements. The old mysql_* functions no longer exist.
  • Transactions use begin_transaction(), commit() and rollback(), with the same try/catch pattern as PDO.

Next: Pagination, Search and Database Patterns