PHP MySQLi

MySQLi ("MySQL Improved") is the PHP extension you use to connect to a MySQL or MariaDB database, run queries, and read the results. This article explains what the extension is, how to connect, how to run SELECT/INSERT/UPDATE queries safely, and when to reach for MySQLi versus PDO.

What is the MySQLi extension?

The MySQLi extension is the successor to the old (and now removed) mysql_* functions. It adds features the original extension never had: prepared statements, transactions, multiple statements, and proper support for MySQL 4.1+ authentication. It ships with PHP and is enabled by default on most installations.

MySQLi exposes the same functionality through two interfaces:

• Object-oriented — you work with a mysqli object and call methods like $mysqli->query(). This is the style most modern code uses, and the one this article follows.
• Procedural — you call functions like mysqli_connect() and mysqli_query() and pass the connection around as the first argument.

Both do exactly the same thing; pick one and stay consistent. The object-oriented form is shorter and reads more naturally.

Connecting to a database

A connection is represented by a mysqli object. You create one by passing the host, username, password, and database name to the constructor, then check that it succeeded before doing anything else:

<?php
$mysqli = new mysqli("localhost", "username", "password", "my_database");

// Always check the connection before using it.
if ($mysqli->connect_error) {
    die("Failed to connect to MySQL: " . $mysqli->connect_error);
}

// Recommended: use UTF-8 so accented characters and emoji are stored correctly.
$mysqli->set_charset("utf8mb4");

echo "Connected successfully";
?>

The connect_error property holds a human-readable message when the connection fails (or null on success). Calling set_charset() right after connecting avoids subtle encoding bugs later on. For a deeper look at opening a connection, see PHP mysqli_connect() and the connect() reference.

Running a SELECT query

Once connected, query() runs SQL and returns a result object you can loop over. fetch_assoc() returns one row at a time as an associative array, or null when there are no more rows:

<?php
$result = $mysqli->query("SELECT name, email FROM users");

if ($result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
        echo "Name: " . $row["name"] . " - Email: " . $row["email"] . "<br>";
    }
} else {
    echo "No users found.";
}

$result->free();   // release the result set
$mysqli->close();  // close the connection when you are done
?>

num_rows tells you how many rows came back, so you can show a friendly message when a query returns nothing. See PHP MySQL Select Data for more query patterns.

Prepared statements (the safe way to use input)

Never paste user input straight into a query string — that is how SQL injection happens. A prepared statement sends the SQL and the data separately, so the database treats input strictly as a value and never as executable SQL.

You write ? placeholders, prepare() the statement, then bind_param() the real values:

<?php
$age = 18;

$stmt = $mysqli->prepare("SELECT name, email FROM users WHERE age > ?");
$stmt->bind_param("i", $age);   // "i" = integer (s = string, d = double, b = blob)
$stmt->execute();

$result = $stmt->get_result();
while ($row = $result->fetch_assoc()) {
    echo "Name: " . $row["name"] . " - Email: " . $row["email"] . "<br>";
}

$stmt->close();
?>

The first argument to bind_param() is a type string: one character per placeholder — i (integer), s (string), d (double/float), or b (blob). The remaining arguments are the values, in order.

Note: get_result() requires the mysqlnd driver, which is the default in modern PHP. If your build does not have it, use bind_result() to bind columns into variables instead.

For a dedicated walkthrough, see PHP MySQL Prepared Statements.

Inserting and updating data

The same prepared-statement pattern works for writes. After an INSERT, the auto-increment id of the new row is available on $mysqli->insert_id; after an INSERT/UPDATE/DELETE, $mysqli->affected_rows tells you how many rows changed:

<?php
$name  = "Ada Lovelace";
$email = "[email protected]";

$stmt = $mysqli->prepare("INSERT INTO users (name, email) VALUES (?, ?)");
$stmt->bind_param("ss", $name, $email);   // two strings
$stmt->execute();

echo "Inserted user #" . $mysqli->insert_id;
$stmt->close();
?>

See PHP MySQL Insert Data and PHP MySQL Update Data for the full CRUD picture.

Handling errors

If a query fails, query() returns false and the details are on the connection object. Reading $mysqli->error after a failed call tells you what went wrong:

<?php
if (!$mysqli->query("SELECT * FROM no_such_table")) {
    echo "Query failed (" . $mysqli->errno . "): " . $mysqli->error;
}
?>

In development you can make MySQLi throw exceptions automatically with mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT), which is the default behavior since PHP 8.1. Related reading: PHP connect_error and PHP connect_errno.

MySQLi vs. PDO — which should I use?

Both are safe, modern ways to talk to a database. The short version:

• MySQLi works only with MySQL/MariaDB. It exposes some MySQL-specific features (like async queries) that PDO does not.
• PDO (PHP Data Objects) speaks a dozen databases through one API, so switching from MySQL to PostgreSQL means changing a connection string, not your queries. It also supports named placeholders (:name) which are easier to read.

If you know you will only ever use MySQL, MySQLi is perfectly fine. If portability matters, prefer PDO. Either way, always use prepared statements.

Summary

• MySQLi is the standard extension for working with MySQL/MariaDB in PHP, available in both object-oriented and procedural styles.
• Create a connection with new mysqli(...), check connect_error, and set the charset.
• Use query() + fetch_assoc() to read data and loop over results.
• Use prepared statements (prepare() → bind_param() → execute()) for anything involving user input to stay safe from SQL injection.
• Check $mysqli->error / $mysqli->errno when a query fails, and prefer PDO when you need database portability.

Practice