Skip to content
Tutorials Center
My learning

Topics

PHP Reference ยท Databases (PDO)

The mysqli extension

Overview of PHP's mysqli extension for MySQL and MariaDB: connecting, bind_param, execute_query(), error reporting, and how each call maps to PDO. Read-only examples.

mysqli ("MySQL improved") is a PHP extension that talks to MySQL and MariaDB only. It offers an object-oriented interface (new mysqli(...)) and a procedural one (mysqli_connect(...)), plus MySQL-specific features such as multiple statements and asynchronous queries. It replaced the removed mysql_* functions and is still common in older code and WordPress plugins.

New projects usually pick PDO because it supports many databases and has named placeholders. Both are safe when you use prepared statements.

Our online editor has no MySQL server and its PHP build has no mysqli extension, so the mysqli code on this page is read-only. The runnable examples check what is available and show the same logic with PDO and SQLite.

PHP
$mysqli = new mysqli('127.0.0.1', 'app', $password, 'shop');
$mysqli->set_charset('utf8mb4');

$stmt = $mysqli->prepare('SELECT id, name FROM users WHERE email = ?');
$stmt->bind_param('s', $email);
$stmt->execute();
$user = $stmt->get_result()->fetch_assoc();

Try it

PHP Is mysqli available here?

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.

PHP The same query with PDO (runnable stand-in)

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.

Syntax

The main pieces (read-only, needs the extension and a MySQL server):

PHP
// Connect (throws mysqli_sql_exception on failure since PHP 8.1)
$mysqli = new mysqli(string $hostname, string $username, string $password, string $database, int $port);

// Plain query (no user data!)
$result = $mysqli->query('SELECT id, title FROM posts');
while ($row = $result->fetch_assoc()) { /* ... */ }

// Prepared statement
$stmt = $mysqli->prepare('INSERT INTO posts (title, views) VALUES (?, ?)');
$stmt->bind_param('si', $title, $views);   // s = string, i = integer, d = double, b = blob
$stmt->execute();
$newId = $mysqli->insert_id;

// One call: PHP 8.2+
$result = $mysqli->execute_query('SELECT * FROM posts WHERE id = ?', [$id]);
Piece Description
new mysqli(...) / mysqli_connect() Opens a connection. Use set_charset('utf8mb4') right after.
query() Runs SQL and returns a mysqli_result (or true/false for statements without rows).
prepare() + bind_param() + execute() Prepared statement with ? placeholders only. Types are given as a string such as 'ssi'.
get_result() Turns a statement result into a mysqli_result (needs the mysqlnd driver, the default).
execute_query() Prepare, bind and execute in one call (PHP 8.2+).
insert_id, affected_rows The generated ID and the number of changed rows.
begin_transaction(), commit(), rollback() Transactions.

Errors / Exceptions: since PHP 8.1 the default report mode is MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT, so failures throw mysqli_sql_exception.

Usage notes

  • Only ? placeholders. mysqli has no named placeholders and bind_param() needs variables (by reference), not literals.
  • real_escape_string() is not a substitute for prepared statements. It depends on the connection charset being set correctly and does not protect identifiers or numbers. Prefer binding; see SQL injection.
  • Close results with free() or close() when you run many queries on one connection.
  • MySQL and MariaDB only. Switching to PostgreSQL or SQLite means rewriting the data layer, which is one reason many teams use PDO.
  • Mapping to PDO: prepare() is PDO::prepare(), fetch_assoc() is fetch(PDO::FETCH_ASSOC), fetch_all(MYSQLI_ASSOC) is fetchAll(), insert_id is lastInsertId().
  • Check the extension with extension_loaded('mysqli'). On Linux it comes with packages such as php-mysql.