mysqli vs PDO in PHP: differences, errors and which to pick

Pick PDO unless you have a specific reason not to. Both extensions are maintained, both talk to MySQL through the same mysqlnd library underneath, both are safe with prepared statements, and the speed difference between them is too small to measure in a real application. PDO wins on the things you touch every day: named placeholders, one API for MySQL, PostgreSQL and SQLite, fetch modes that hydrate rows into arrays or objects without a loop, and exceptions on by default since PHP 8.0. mysqli wins only where you need something MySQL-specific that PDO does not expose - asynchronous queries, multi_query() with its separate result sets, or a codebase already written in it that works. The rest of this post is the detail behind that verdict: how the two APIs actually differ, prepared statements side by side, the error-reporting change in PHP 8.1 that broke a lot of old mysqli code, what "performance" means here, and how to move legacy mysql_* code onto either.

The short version, as a table#

None of those rows is a reason to rewrite working code. They are reasons to choose one for new code, and the choice for new code is PDO in nearly every case. If you are already using PDO and want the full treatment of the DSN, options, transactions and error codes, PHP PDO and MySQL covers it; this post stays on the comparison.

What the two extensions are#

Three MySQL extensions have existed in PHP, and the history explains the naming.

The original mysql extension - mysql_connect(), mysql_query(), mysql_fetch_assoc() - was deprecated in PHP 5.5 and removed entirely in PHP 7.0. It had no prepared statements, kept an implicit "last connection" in global state, and pushed a generation of developers into building SQL by concatenating strings. If a codebase still calls mysql_query(), it is running on PHP 5.6 or older, or it is not running at all.

mysqli (the "i" is for "improved") arrived in PHP 5 as the replacement. It is a fairly thin wrapper over MySQL's own client API: it exposes MySQL concepts directly, supports prepared statements, transactions, multiple statements and asynchronous queries, and offers both an object interface ($mysqli->query()) and a procedural one (mysqli_query($link, ...)). The procedural interface exists mainly to make porting old mysql_* code easier.

PDO (PHP Data Objects) arrived around the same time with a different goal: one database access API, with per-database drivers plugged in underneath. pdo_mysql is the MySQL driver. PDO does not try to expose everything MySQL can do. It exposes what most applications need, consistently, across databases.

Both extensions use mysqlnd, the MySQL native driver written for PHP, in every mainstream build. That matters for the performance question later: the network protocol, the buffering and the result handling are the same code in both cases.

Drivers: one database or many#

PDO's driver list is the clearest functional difference. The common ones:

You can check what a PHP install has with php -m, or php -r 'print_r(PDO::getAvailableDrivers());'.

The honest version of the portability argument: PDO does not make SQL portable. LIMIT versus TOP, AUTO_INCREMENT versus identity columns, backtick quoting versus double quotes - all of that is still yours to deal with. What PDO makes portable is the code around the SQL: connecting, binding, fetching, transactions and error handling. In practice that is most of the database code in an application, and it is why every major PHP framework and ORM - Laravel's Eloquent, Doctrine DBAL, CakePHP's ORM - is built on PDO. It is also why running the test suite against SQLite while production runs MySQL is a realistic option with PDO and a non-starter with mysqli.

If an application will only ever talk to MySQL, portability is worth little on its own. The case for PDO then rests on the API being pleasanter, which it is, mostly because of placeholders and fetch modes.

Prepared statements in both#

The same query - find active users created after a date - written three ways.

mysqli, the classic form. Parameters are bound by reference with a type string: i integer, d double, s string, b blob sent in packets.

$stmt = $mysqli->prepare(    'SELECT id, email FROM users WHERE status = ? AND created_at > ?');$stmt->bind_param('ss', $status, $since);$stmt->execute();$result = $stmt->get_result();$users = $result->fetch_all(MYSQLI_ASSOC);

mysqli on PHP 8.1 and later can skip bind_param() and pass an array to execute(); every value is then bound as a string, which MySQL converts as needed. PHP 8.2 added execute_query(), which prepares, binds and executes in one call and returns a result:

// PHP 8.1+$stmt = $mysqli->prepare('SELECT id, email FROM users WHERE status = ? AND created_at > ?');$stmt->execute([$status, $since]);// PHP 8.2+$users = $mysqli->execute_query(    'SELECT id, email FROM users WHERE status = ? AND created_at > ?',    [$status, $since])->fetch_all(MYSQLI_ASSOC);

PDO, with named placeholders:

$stmt = $pdo->prepare(    'SELECT id, email FROM users WHERE status = :status AND created_at > :since');$stmt->execute(['status' => $status, 'since' => $since]);$users = $stmt->fetchAll(PDO::FETCH_ASSOC);

The differences that bite in practice:

• Named placeholders. PDO has them; mysqli does not. On a query with eight parameters, :email is readable and a type string of ssisdssi is a bug waiting for someone to add a column in the middle.
• Binding by reference. bind_param() binds variables, not values, and reads them at execute() time. That is useful in a loop (bind once, change the variables, execute repeatedly) and confusing everywhere else, because you cannot pass a literal or a function result directly. PDO has both bindParam() (by reference) and bindValue() (by value), and execute($array) covers most cases.
• `get_result()` versus `bind_result()`. get_result() returns a normal result object you can fetch arrays from. It needs mysqlnd, which is why old tutorials written for builds without it use bind_result() to bind each output column to a variable. On any current PHP you can use get_result() and forget bind_result() exists.
• Server-side or emulated. mysqli prepares are always real server-side prepared statements. PDO with MySQL emulates them by default - it escapes values and sends one finished query - unless you set PDO::ATTR_EMULATE_PREPARES to false. Both are safe when the connection character set is set properly. The PDO post explains when emulation matters.

Neither API lets a placeholder stand in for a table name, a column name or ASC/DESC. Those must come from a fixed list in your code, whichever extension you use.

Error reporting: what changed in PHP 8.0 and 8.1#

This is the difference most likely to break an upgrade, and it changed in both extensions within a year.

PDO defaulted to PDO::ERRMODE_SILENT until PHP 8.0. A failed query returned false, set an error code you had to ask for, and the script carried on. PHP 8.0 changed the default to PDO::ERRMODE_EXCEPTION, so a failed query throws PDOException.

mysqli followed in PHP 8.1. Before 8.1 the default report mode was MYSQLI_REPORT_OFF: query() returned false, and you checked $mysqli->error or $mysqli->errno yourself - or, as most code did, did not check at all. From PHP 8.1 the default is MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT, which throws mysqli_sql_exception on any error, including a failed connection.

Old mysqli code typically looks like this:

$link = mysqli_connect($host, $user, $pass, $db);if (!$link) {    die('Connect failed: ' . mysqli_connect_error());}$result = mysqli_query($link, $sql);if ($result === false) {    error_log(mysqli_error($link));    // fall back to something}

On PHP 8.1 the if blocks never run: the exception is thrown first, and if nothing catches it, the request dies with an uncaught mysqli_sql_exception. With display_errors on - which it should not be in production - that message can include the host name and user. Code that relied on a failed INSERT returning false so it could show "that name is taken" now crashes instead.

You have two ways forward. The right one is to keep exceptions and catch the errors you expect:

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT); // explicit, same as 8.1 defaulttry {    $mysqli->execute_query('INSERT INTO users (email) VALUES (?)', [$email]);} catch (mysqli_sql_exception $e) {    if ($e->getCode() === 1062) {   // duplicate entry        return 'That email is already registered.';    }    throw $e;}

The stopgap is to put the old behaviour back with mysqli_report(MYSQLI_REPORT_OFF); right after startup. It buys time on a large legacy codebase, but it also brings back the silent failures, so treat it as a temporary setting with a ticket attached.

One small difference in what you branch on: mysqli_sql_exception::getCode() returns the MySQL error number (1062, 1045, 2002), and getSqlState() returns the SQLSTATE. PDOException::getCode() returns the SQLSTATE string (23000), and the MySQL number lives in $e->errorInfo[1]. Branch on the MySQL number in both; one SQLSTATE covers many unrelated errors. MySQL users and privileges explains the common 1045 Access denied cases.

Types, fetching and the features only one has#

Integers that come back as strings

With mysqli, a plain query() uses MySQL's text protocol and returns every column as a string, even INT columns. Prepared statements use the binary protocol and return native integers and floats. You can make plain queries return native types too:

$mysqli = mysqli_init();$mysqli->options(MYSQLI_OPT_INT_AND_FLOAT_NATIVE, true);$mysqli->real_connect($host, $user, $pass, $db, $port);

PDO with native prepares returns integers and floats; with emulated prepares it returned strings until PHP 8.1, which changed it to native types. In both extensions DECIMAL comes back as a string on purpose, so money does not pick up floating-point rounding. When an upgrade suddenly breaks a strict comparison like $row['id'] === '5', this is usually why.

Fetching

PDO's fetch modes save loops: FETCH_COLUMN for a flat list, FETCH_KEY_PAIR for an id-to-name map, FETCH_GROUP for rows grouped by the first column, FETCH_CLASS to hydrate objects. mysqli has fetch_assoc(), fetch_row(), fetch_object(), fetch_all() and, since PHP 8.1, fetch_column(). Anything beyond that is a loop you write.

A mysqli_result is iterable, so foreach ($result as $row) gives associative arrays without calling fetch_assoc() in a while loop. A PDO statement is iterable the same way, in whatever fetch mode is set.

What only mysqli does

• Asynchronous queries. $mysqli->query($sql, MYSQLI_ASYNC) returns immediately; mysqli_poll() waits on several connections and reap_async_query() collects the result. This lets one PHP process run a few slow queries in parallel on separate connections. It is rarely used, and when it is, it is usually for reports that fan out over several databases.
• `multi_query()`. Runs several semicolon-separated statements in one call and walks the results with next_result(). Useful for running a migration file. It is also exactly what an SQL injection wants, so it should never see user input. PDO with emulated prepares can run multiple statements in one exec(), but handling their results is awkward.
• MySQL-specific calls such as $mysqli->info (rows matched and changed after an UPDATE), stat(), thread_id and kill().

What only PDO does

Named placeholders, the fetch modes above, FETCH_CLASS with constructor arguments, and the same code on several databases. Since PHP 8.4 there are also driver subclasses: PDO::connect() returns a Pdo\Mysql object for a MySQL DSN, with the MySQL-specific constants on it.

Performance: what actually differs#

Benchmarks of mysqli against PDO tend to show mysqli a few percent faster on tight loops of trivial queries. That number is real and irrelevant. Both extensions hand the work to mysqlnd; the difference is a thin layer of PHP-side wrapping per call, measured in microseconds. A single query that reads from disk instead of the buffer pool costs more than the gap across thousands of calls.

The things that do change how fast your database code runs:

• Round trips. A server-side prepared statement is a prepare request and then an execute request: two trips to the server before results come back. mysqli always does that for prepare() and execute_query(). PDO with emulated prepares sends one query. Over a local socket nobody notices; over a network path of a couple of milliseconds, a page that runs 200 one-off prepared queries spends real time waiting. Either fix the page so it does not run 200 queries, or prepare once and execute in a loop, which both extensions do well.
• Buffered versus unbuffered results. Both buffer the full result in PHP memory by default. For a large export, mysqli's MYSQLI_USE_RESULT (or real_query() plus use_result()) and PDO's MYSQL_ATTR_USE_BUFFERED_QUERY => false stream rows instead. This changes memory use by orders of magnitude; the extension choice does not.
• Connections. Opening a connection per helper function instead of once per request multiplies handshakes. That costs the same in both. MySQL connection limits and pooling covers why PHP-FPM workers and max_connections need to agree.
• Indexes. Nearly every "PHP is slow with MySQL" report is a missing index. MySQL indexes and EXPLAIN is the place to start, and the slow query log tells you which query to start with.

If you are choosing between the two extensions for speed, you are optimising the wrong layer.

Migrating legacy mysql_* code#

Code still built on mysql_* functions has to move before it can run on any supported PHP. The mechanical route is the procedural mysqli API, because the function names line up; the better route, if you are touching every query anyway, is PDO with prepared statements. Either way, the work is the same three steps: replace the calls, pass the connection explicitly, and replace string-built SQL with placeholders.

The mapping for the common calls:

The traps in a straight find-and-replace:

1. Argument order. mysql_query($sql, $link) took the link second and optionally. mysqli_query($link, $sql) takes it first and always. A regex that renames the function without swapping the arguments produces code that fails on every call.
2. The implicit connection. mysql_* functions used the last connection opened if you passed none, so legacy code calls mysql_query() from deep inside functions that never saw a connection variable. mysqli and PDO need it passed in. The quick fix is a function that returns a shared connection (db()->query(...)); a global variable works too and is worse.
3. Functions with no equivalent. mysql_result() (fetch one cell by row and field) has no mysqli twin; replace it with a fetch and an array index, or PDO's fetchColumn(). mysql_list_dbs() and friends become SHOW DATABASES queries.
4. Escaping without a charset. If the old code calls mysql_real_escape_string() and you swap it for mysqli_real_escape_string(), set the charset first with mysqli_set_charset($link, 'utf8mb4'), never with a SET NAMES query - escaping only knows the charset it was told through the API. Better still, delete the escaping and use placeholders.
5. Error handling flips. Legacy or die(mysql_error()) patterns become dead code under PHP 8.1 exceptions. Decide on one error strategy at the start of the port rather than discovering it query by query.

Shim libraries exist that reimplement the mysql_* functions on top of mysqli so old code runs unchanged on PHP 7 and 8. They are a way to get an abandoned application onto a supported PHP version while you plan the real migration, not a destination: the code is still building SQL from strings.

Old code usually comes with an old database. MySQL 8.4 no longer enables the mysql_native_password plugin by default, and very old PHP builds cannot authenticate with its replacement, caching_sha2_password, so a legacy app moved onto a new database can fail at connect with an authentication error. MySQL 8.4 LTS: what changed covers the fix, and migrating MySQL to a new host covers moving the data.

Connecting to a hosted database from either#

The connection rules are the same in both extensions, because the client library underneath is the same.

// mysqli$mysqli = new mysqli($host, $user, $pass, $db, (int) $port);$mysqli->set_charset('utf8mb4');// PDO$pdo = new PDO(    "mysql:host=$host;port=$port;dbname=$db;charset=utf8mb4",    $user,    $pass,    [PDO::ATTR_EMULATE_PREPARES => false]);

Three things hold for both. Set utf8mb4 through the API - set_charset() or the DSN - so escaping and emoji both work (MySQL utf8mb4 and collations covers the tables). Do not write localhost for a database on another machine, because the client treats localhost as a request for a local Unix socket and ignores the port. And keep the credentials in environment variables rather than in a PHP file inside the web root; environment variables and secrets explains where they belong.

Persistent connections exist in both - a p: prefix on the mysqli host, PDO::ATTR_PERSISTENT in PDO - and the same caution applies: they carry session state from one request to the next on the same worker, and they rarely save enough to be worth it.

On RE:NODE, a MySQL 8.4 LTS database is created with a root password, an application database and an application user already generated, and it is reached on the plan's host and port - so either connection above is those four values pasted in. The Laravel / PHP app line runs PHP 8.3, 8.4 or 8.5, which means every default described in this post (exceptions in both extensions, execute_query(), native types from emulated PDO prepares) is the behaviour you will see there.

FAQ#

Is mysqli deprecated?

No. mysqli is maintained and gets new features - execute_query() arrived in PHP 8.2. What was deprecated and removed is the old mysql extension without the "i". Choosing PDO for new code is a preference, not a migration you are forced into.

Is mysqli less secure than PDO?

No. Both are equally safe when every outside value goes through a placeholder, and equally unsafe when SQL is built by concatenation. mysqli's prepares are always server-side, so there is no emulation layer to configure. The security of either depends on the habits around it, not the extension.

Can I use mysqli and PDO in the same application?

Yes. They are separate connections, so they do not share transactions, lastInsertId() or session variables, but they can both read and write the same database. That is the usual way to migrate gradually from one to the other.

Does PDO work with MariaDB?

Yes. pdo_mysql and mysqli both speak the MySQL protocol, which MariaDB implements. The differences you meet are in SQL features and server defaults, not in the PHP extension.

Why does my mysqli code throw exceptions after upgrading PHP?

PHP 8.1 changed mysqli's default report mode from off to MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT. Queries that used to return false quietly now throw mysqli_sql_exception. Catch the errors you expect, or call mysqli_report(MYSQLI_REPORT_OFF) as a short-term stopgap.

Which one do frameworks use?

PDO. Laravel, Symfony through Doctrine DBAL, CakePHP and most other frameworks build their database layer on it, which is one more reason new code that may end up inside a framework should start there.