In PHP, what are the practical differences between the mysqli extension and PDO, and when would you choose mysqli?
answer
- one database versus many drivers
- ? only versus :named placeholders
- procedural and object styles
- multi_query and async queries
- legacy code already on mysqli
basics
~20 smysqli works only with MySQL, offers procedural and object styles, positional ? placeholders and MySQL-specific features such as multi_query(). PDO is one object API across many databases with named placeholders. Choose mysqli for existing mysqli code or MySQL-only features.
solid answer
~40 sBoth are maintained, both support prepared statements and transactions, and both throw exceptions by default in current PHP. **mysqli** is MySQL-only, comes in an object style and a procedural style (`mysqli_query($link, ...)`), binds with `?` placeholders and a type string, and exposes MySQL-specific features like `multi_query()` and asynchronous queries. **PDO** is a single object API over many drivers, supports named placeholders such as `:room_id`, and has fetch modes that hydrate objects. For new code I default to PDO; I keep mysqli when an app already uses it, like a legacy timetable system, or when I need a MySQL-only feature. Neither makes the SQL itself portable.
code
php · 15 lines<?php
declare(strict_types=1);
// mysqli, object style
$db = new mysqli('db', 'app', $password, 'timetable');
$stmt = $db->prepare('SELECT room, starts_at FROM lessons WHERE teacher_id = ?');
$stmt->bind_param('i', $teacherId);
$stmt->execute();
$rows = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
// PDO
$pdo = new PDO('mysql:host=db;dbname=timetable;charset=utf8mb4', 'app', $password);
$stmt = $pdo->prepare('SELECT room, starts_at FROM lessons WHERE teacher_id = :teacher');
$stmt->execute(['teacher' => $teacherId]);
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);go deeper
Recall that mysqli is MySQL-only with procedural and object styles, while PDO is one object API over many databases with named placeholders.
Explain what each gives you beyond the basics: bind_param type strings and multi_query() in mysqli, fetch modes and bindValue() in PDO.
Argue the choice for a real codebase: keep mysqli in a legacy app and spend the effort on prepared statements, or pick PDO for new code that may change databases.
Decide whether a team standardises on one API across services, weighing a uniform data layer against rewriting working mysqli code.
## Two APIs for the same server PHP ships two extensions that talk to MySQL: - **mysqli** ("MySQL improved") is MySQL-specific. It exposes the server's features closely and offers two interchangeable styles: an object API (`$mysqli->query(...)`) and a procedural API (`mysqli_query($link, ...)`). - **PDO** (PHP Data Objects) is a database-neutral layer. The same classes, `PDO` and `PDOStatement`, work with MySQL through `pdo_mysql` and with PostgreSQL, SQLite and other databases through their own drivers. Both are current, maintained and bundled with PHP. Neither is deprecated, and neither is the old `mysql_*` extension, which was removed in PHP 7.0. ## What they share | Capability | mysqli | PDO | |---|---|---| | Prepared statements with bound values | yes | yes | | Transactions | `begin_transaction()`, `commit()`, `rollback()` | `beginTransaction()`, `commit()`, `rollBack()` | | Exceptions on failure by default | `mysqli_sql_exception` (PHP 8.1+) | `PDOException` (PHP 8.0+) | | Persistent connections | yes | yes | ## Where they differ | Aspect | mysqli | PDO | |---|---|---| | Databases | MySQL and compatible servers only | many, one driver each | | Placeholders | positional `?` only | positional `?` and named `:teacher_id` | | Binding | a type string, `bind_param('is', ...)`, or a list array | `bindValue()`, `bindParam()` or an array to `execute()` | | API style | object and procedural | object only | | Fetching | `mysqli_result` methods such as `fetch_assoc()` | fetch modes, including hydrating objects of a class | | MySQL-only features | `multi_query()`, asynchronous queries with `MYSQLI_ASYNC` and `mysqli::poll()` | not exposed through the generic API | ## Procedural versus object style The two mysqli styles are the same functions under two spellings. `mysqli_query($link, $sql)` and `$link->query($sql)` do the same thing; the procedural form takes the connection as its first argument. The object style is the norm in current code because it reads like the rest of a modern PHP codebase and works with type declarations (`mysqli $db`). Legacy apps often use procedural calls throughout, and there is no functional reason to rewrite them just to change style. ## How to choose 1. **New code with no strong constraint:** PDO. One API across databases, named placeholders, and fetch modes that map rows to objects. Many PHP database libraries build on it. 2. **An existing app built on mysqli:** keep mysqli. A school-timetable app written against `mysqli_*` functions gains nothing from a rewrite of its data layer; the valuable work is moving its queries to prepared statements. 3. **A MySQL-specific need:** mysqli. Running a multi-statement SQL script with `multi_query()`, or firing several queries asynchronously and collecting them with `mysqli::poll()`, has no PDO equivalent. 4. **A possible database change later:** PDO. It does not make SQL portable — dialects still differ — but it keeps the PHP side unchanged. ## What moving between them costs Switching an existing app from one API to the other is a real rewrite of its data layer, not a search-and-replace: - every `?` query with `bind_param()` becomes an `execute()` call with an array, or the reverse; - result handling changes from `mysqli_result` methods to PDO fetch modes; - error handling changes class, from `mysqli_sql_exception` to `PDOException`, in every `catch`; - connection setup moves from host, user, password and database arguments to a DSN string. That cost is why "keep mysqli in the legacy app" is usually the right call: the same effort spent converting interpolated queries to prepared statements removes real risk, while an API swap mostly moves code around. ## Misconceptions interviewers listen for - "mysqli is deprecated" — no; the removed extension is `mysql_*`, without the *i*. - "Only PDO has prepared statements" — mysqli has had them all along. - "PDO makes the application database-independent" — it makes the **API** independent; the SQL is still written for one server. - "mysqli is much faster" — performance is not a real differentiator for typical web queries; the choice is about API and features. ## The one-sentence answer PDO is the portable default with named placeholders and richer fetching; mysqli is MySQL-only, offers a procedural style and MySQL-specific features, and is the sensible choice for code that already uses it.
- Is the procedural mysqli style deprecated in PHP 8.5?No. Functions such as `mysqli_query()` and `mysqli_stmt_bind_param()` are the same implementation as the methods. PHP 8.5 deprecates only `mysqli_execute()`, an alias of `mysqli_stmt_execute()`; the alias goes, the function it points to stays.
- Does switching from mysqli to PDO let the app move from MySQL to PostgreSQL without changes?Only the PHP calls stay the same. The SQL still uses MySQL syntax and behaviour, such as backtick-quoted identifiers or `LIMIT` forms, and has to be reviewed. PDO removes one layer of the migration, the API, not the dialect.
saying these in an interview costs you the question
- Saying mysqli is deprecated, confusing it with the removed mysql_* extension.
- Claiming only PDO supports prepared statements.
- Believing PDO makes SQL portable between database servers.
- Choosing mysqli for speed without any measurement.
- Thinking procedural mysqli calls lack features the object style has.