MySQLi Extension

mysqli — short for "MySQL Improved" — is PHP's dedicated extension for talking to MySQL and MariaDB databases. It replaced the very old mysql_* functions (removed from PHP years ago) and, unlike PDO, it offers two completely separate ways of writing the same code: a procedural style that will look familiar if you've used the old mysql_* functions, and an object-oriented style built around a mysqli object. This page covers both, plus prepared statements, which work a little differently here than they do in PDO.

Procedural vs object-oriented style

Procedural mysqli passes the connection as the first argument to every function, mirroring how the extension used to work before it gained an object-oriented API. Object-oriented mysqli wraps the same functionality in a mysqli object and calls it via methods instead. Both styles are fully supported and call into the same underlying code — the choice is purely about which reads better in your codebase.

Procedural style

PHP

<?php
$link = mysqli_connect('localhost', 'app_user', 'secret', 'shop');

if (!$link) {
    die('Connection failed: ' . mysqli_connect_error());
}

$result = mysqli_query($link, 'SELECT id, name FROM products');

Object-oriented style

PHP

<?php
$mysqli = new mysqli('localhost', 'app_user', 'secret', 'shop');

if ($mysqli->connect_errno) {
    die('Connection failed: ' . $mysqli->connect_error);
}

$result = $mysqli->query('SELECT id, name FROM products');

The object-oriented style is the one you'll see recommended most often today, since it groups the connection and its related methods together and tends to read more consistently alongside PDO code, in codebases that use both.

Running a simple query and reading the results

->query() sends SQL straight to the server and, for a SELECT, returns a mysqli_result object you can loop over. ->fetch_assoc() pulls one row at a time back as an associative array keyed by column name, and returns null once there are no rows left — which makes it a natural fit for a while loop.

Fetching rows with fetch_assoc()

PHP

<?php
$result = $mysqli->query('SELECT id, name, price FROM products');

while ($row = $result->fetch_assoc()) {
    echo $row['name'] . ': $' . $row['price'] . PHP_EOL;
}

Wireless Mouse: $19.99
USB-C Cable: $8.50
Mechanical Keyboard: $74.00

->query() is fine for fixed SQL with no variables in it, such as a static report or a schema check. The moment a query needs to include a value that came from a form, a URL parameter, or anywhere else outside your own code, ->query() is the wrong tool — that's what prepared statements are for.

Prepared statements with mysqli

mysqli's prepared statement API is more manual than PDO's: you prepare the SQL with placeholders, bind variables to those placeholders with an explicit type string, execute, and then fetch the results from the statement rather than from a plain query result.

Never build SQL by concatenating user input — even with mysqli

It's tempting to write "SELECT * FROM users WHERE email = '" . $_GET['email'] . "'", but this lets anyone control the query itself. If someone sends ' OR '1'='1 as the email, the resulting SQL becomes a query that matches every row in the table. This is SQL injection, and it applies just as much to mysqli as it does to any other way of running SQL. The fix, shown below, is to send the value separately from the query text using a prepared statement.

Safe lookup with prepare() / bind_param() / execute()

PHP

<?php
$email = $_GET['email'] ?? '';

$stmt = $mysqli->prepare('SELECT id, name, email FROM users WHERE email = ?');
$stmt->bind_param('s', $email); // 's' = string parameter
$stmt->execute();

$result = $stmt->get_result(); // requires the mysqlnd driver
$user = $result->fetch_assoc();

$stmt->close();

The single quote-style character passed to bind_param() ('s' here) tells mysqli how to treat each bound value: i for integer, d for double, s for string, and b for binary data. With multiple placeholders, the type string simply grows to match, in order — for example 'sis' for a string, then an integer, then another string.

Inserting a row with multiple bound parameters

PHP

<?php
$stmt = $mysqli->prepare(
    'INSERT INTO products (name, price, in_stock) VALUES (?, ?, ?)'
);
$stmt->bind_param('sdi', $name, $price, $inStock);

$name = 'Wireless Mouse';
$price = 19.99;
$inStock = 1;

$stmt->execute();
echo 'New product id: ' . $stmt->insert_id;

$stmt->close();

New product id: 42

get_result() needs the mysqlnd driver

`->get_result()` relies on the `mysqlnd` (MySQL Native Driver) backend, which is the default on virtually every modern PHP install. On the rare setup without it, results are read instead with `->bind_result()` followed by `->fetch()`, which binds each selected column to its own PHP variable — noticeably more verbose for multi-column queries.

When mysqli still makes sense

Given that PDO covers everything mysqli does and adds database-agnostic support on top, reaching for mysqli in new code is uncommon today. It's still a reasonable choice when:

You are maintaining an existing codebase already built on mysqli, and rewriting it to PDO isn't worth the churn.
You need a MySQL-specific feature exposed only through mysqli, such as multi-statement queries via ->multi_query().
You are following a tutorial, course, or team convention that specifically targets mysqli.

Outside of those situations, PDO is the extension covered as the default across the rest of this database section.

Tip

If you do use mysqli, pick the object-oriented style and stay consistent with it throughout a project. Mixing procedural and object-oriented calls on the same connection works, but it makes code harder to scan and easier to misuse — for instance, forgetting which of `mysqli_query($link, ...)` or `$mysqli->query(...)` a given file expects.