skip to content

Database Access

1 roadmap25 questions

PDO and mysqli connect PHP to MySQL, PostgreSQL and SQLite: fetch modes, prepared statements, transaction calls and persistent links. Interviewers probe it to see safe, leak-free data code.

part ofPHPoverview, primer and where to startread it →
on this pageshow

explore

questions

25

In PHP, what are the practical differences between the mysqli extension and PDO, and when would you choose mysqli?

level: juniorimportance: must knowfreq 66%

answer

  1. one database versus many drivers
  2. ? only versus :named placeholders
  3. procedural and object styles
  4. multi_query and async queries
  5. legacy code already on mysqli

basics

~20 s

mysqli 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 s

Both 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
<?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

for a junior

Recall that mysqli is MySQL-only with procedural and object styles, while PDO is one object API over many databases with named placeholders.

for a middle

Explain what each gives you beyond the basics: bind_param type strings and multi_query() in mysqli, fetch modes and bindValue() in PDO.

for a senior

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.

for a principal

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.
open as a page

In PHP, how do you open a PDO connection to MySQL, and what should its options array set?

level: juniorimportance: must knowfreq 72%

basics

~10 s

Call new PDO($dsn, $user, $password, $options) with a DSN such as mysql:host=db;dbname=clinic;charset=utf8mb4. Set PDO::ATTR_ERRMODE to ERRMODE_EXCEPTION and PDO::ATTR_DEFAULT_FETCH_MODE to FETCH_ASSOC; a failed connection throws PDOException.

open as a page

In PHP, how do PDO::prepare() and execute() run a query with user input, and how do ? and :name placeholders differ?

level: juniorimportance: must knowfreq 76%

basics

~20 s

PDO::prepare() turns an SQL template with placeholders into a PDOStatement; execute() supplies the values, a list for ? markers or a keyed array for :name markers. The styles cannot be mixed, and a marker is one whole value.

open as a page

In PHP, what do PDO's ERRMODE_SILENT, ERRMODE_WARNING and ERRMODE_EXCEPTION do, and why did PHP 8.0's default change matter?

level: middleimportance: must knowfreq 60%

basics

~20 s

SILENT makes failing calls return false and leaves details in errorInfo(); WARNING also emits E_WARNING; EXCEPTION throws PDOException. PHP 8.0 made EXCEPTION the default, so a failed query no longer slips by as an unchecked false.

open as a page

In PHP's PDO, how does bindParam() differ from bindValue(), and when does the PDO::PARAM_* type argument change what the database receives?

level: middleimportance: must knowfreq 56%

basics

~20 s

bindValue() copies a value when called; bindParam() binds a variable by reference and reads it at execute(). The PARAM_* type, PARAM_STR by default, decides how the value is sent, which matters for LIMIT, booleans and binary data.

open as a page

With PDO, how do you run the two UPDATEs of a loyalty-points transfer as one transaction that rolls back and still reports the failure?

level: middleimportance: must knowfreq 62%

basics

~10 s

Call $pdo->beginTransaction() before a try block, run both UPDATEs and commit() inside it, and in catch (Throwable $e) call rollBack() when inTransaction() is true, then rethrow so the caller still sees the failure.

open as a page

In PHP, how do you request a persistent database connection with PDO and with mysqli, and what does 'persistent' mean there?

level: juniorimportance: should knowfreq 42%

basics

~20 s

Pass PDO::ATTR_PERSISTENT => true in PDO's constructor options, or prefix the mysqli host with p:. The connection then stays open in that worker process after the request and is reused by its next request with the same credentials.

open as a page

With PDO, how do you get the auto-increment id of the row you just inserted, and what does lastInsertId() return?

level: juniorimportance: should knowfreq 48%

basics

~20 s

Call $pdo->lastInsertId() on the connection right after the INSERT. It returns the generated id as a string, scoped to that connection, so other requests cannot change it; on PostgreSQL you may pass the sequence name.

open as a page

With PHP's mysqli prepared statements, what does the type string passed to bind_param() mean, and why must its arguments be variables?

level: middleimportance: should knowfreq 42%

basics

~20 s

Each letter of bind_param()'s type string gives one placeholder's type: i integer, d double, s string, b blob. The values are bound by reference and read when execute() runs, so only variables can be passed.

open as a page

Since PHP 8.1, what does mysqli do by default when a query fails, and what breaks in legacy code that checks return values?

level: middleimportance: should knowfreq 45%

basics

~10 s

Since PHP 8.1 the default report mode is MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT, so failed connects and queries throw mysqli_sql_exception. Legacy checks like if ($res === false) or if (!$link) never run.

open as a page

In PHP 8.5, how do PDO DSNs for MySQL and PostgreSQL differ, and what does PDO::connect() return that new PDO() does not?

level: middleimportance: should knowfreq 30%

basics

~20 s

Each DSN starts with its driver prefix and takes driver-specific keys: charset for MySQL, sslmode for PostgreSQL. PDO::connect(), added in 8.4, returns the driver subclass such as Pdo\Mysql or Pdo\Pgsql, while new PDO() returns a generic PDO object.

open as a page

In PHP, what do PDO's FETCH_ASSOC, FETCH_OBJ, FETCH_COLUMN and FETCH_KEY_PAIR fetch modes return, and when does each fit?

level: middleimportance: should knowfreq 52%

basics

~10 s

FETCH_ASSOC gives name-keyed arrays, FETCH_OBJ stdClass objects, FETCH_COLUMN a flat list of one column, and FETCH_KEY_PAIR a map from the first of exactly two columns to the second. The default, FETCH_BOTH, duplicates every value.

open as a page

Why is a PHP persistent database connection under PHP-FPM not a connection pool, and how many connections does each worker hold?

level: middleimportance: should knowfreq 40%

basics

~20 s

Each PHP-FPM worker serves one request at a time and keeps its own persistent connection per distinct key, so nothing is shared or borrowed. It is a per-process cache: one connection per worker per DSN and credentials, idle or busy.

open as a page

For a PHP product search, how do you bind a variable-length IN list and LIMIT/OFFSET pagination with PDO placeholders?

level: middleimportance: should knowfreq 50%

basics

~20 s

Generate one ? per IN-list element, skip the predicate when the list is empty, and re-index values with array_values(). Bind LIMIT and OFFSET with bindValue(..., PDO::PARAM_INT) after clamping the page size and deriving the offset.

open as a page

A PHP product listing prepares ORDER BY ? and binds the user's chosen column name: why are results unsorted, with no PDO error?

level: middleimportance: should knowfreq 36%

basics

~20 s

A placeholder always carries a value, never an identifier, so ORDER BY ? bound to 'price' sorts by the constant string 'price', the same for every row. Map the user's choice to a fixed column and direction in PHP instead.

open as a page

With PDO, what does PDOStatement::rowCount() report after an UPDATE, and why can it return 0 for an existing row on MySQL?

level: middleimportance: should knowfreq 40%

basics

~20 s

rowCount() returns the rows affected by the statement's last DELETE, INSERT or UPDATE. On MySQL an UPDATE that writes the values a row already holds changes nothing, so it reports 0 unless the connection sets Pdo\Mysql::ATTR_FOUND_ROWS.

open as a page

In a legacy PHP app, what does mysqli::real_escape_string() actually protect, and where does it fail even when called on every input?

level: seniorimportance: should knowfreq 38%

basics

~20 s

mysqli::real_escape_string() only makes a value safe inside a quoted MySQL string literal on a connection whose charset was set with set_charset(). It does nothing for unquoted numbers, identifiers, LIKE wildcards or a charset changed by SET NAMES.

open as a page

With PDO::FETCH_CLASS in PHP 8.5, when does the constructor run relative to property hydration, and why does that clash with constructor-promoted readonly classes?

level: seniorimportance: should knowfreq 32%

basics

~20 s

FETCH_CLASS assigns columns to same-named properties first, then calls the constructor with only the ctorArgs you supplied; FETCH_PROPS_LATE reverses that. Promoted readonly parameters then get no arguments or hit already-initialized properties, so the fetch throws.

open as a page

In PHP, what state can a reused PDO persistent connection carry over from an earlier request, and how does mysqli's p: prefix handle it differently?

level: seniorimportance: should knowfreq 28%

basics

~20 s

A reused PDO persistent connection keeps session state from the earlier request: SET variables, temporary tables, the selected database and locks. mysqli's p: links run a change-user cleanup on reuse that rolls back, drops temp tables, unlocks and resets session variables.

open as a page

After a news site raised its PHP-FPM worker count, MySQL began refusing logins with 'Too many connections'; how do PDO persistent connections cause this, and how do you fix it?

level: seniorimportance: should knowfreq 35%

basics

~20 s

Each FPM worker keeps its own persistent connection per DSN and user, even while idle, so connections grow to servers × workers × keys and overran MySQL's limit. Resize workers, disable persistence or add a pooling proxy.

open as a page

In PHP, what changes when PDO::ATTR_EMULATE_PREPARES is on versus off, and why do many MySQL projects turn it off?

level: seniorimportance: should knowfreq 42%

basics

~20 s

With emulation on, PDO quotes each value into the SQL and sends one query; with it off, the driver sends template and values separately. pdo_mysql emulates by default; turning it off gives server-checked templates and typed parameters.

open as a page

With PDO on MySQL in PHP 8, why does rollBack() throw 'There is no active transaction' after an ALTER TABLE ran inside the transaction?

level: seniorimportance: should knowfreq 30%

basics

~20 s

MySQL implicitly commits the open transaction when DDL such as ALTER TABLE runs. Since PHP 8.0, pdo_mysql reads the server's real state, so inTransaction() is false and rollBack() throws; the earlier writes are already committed.

open as a page

PDO throws when beginTransaction() is called inside an open transaction; how do you give PHP code nested-transaction behaviour with savepoints?

level: seniorimportance: should knowfreq 28%

basics

~20 s

A PDO connection holds one transaction, and a second beginTransaction() throws PDOException. Nest by hand: open the real transaction at depth 0 and issue SAVEPOINT, ROLLBACK TO SAVEPOINT and RELEASE SAVEPOINT through exec() at deeper levels.

open as a page

In PHP 8.2 and later, what does mysqli::execute_query() replace, and how does it bind the values you pass it?

level: middleimportance: nice to knowfreq 22%

basics

~10 s

mysqli::execute_query() (PHP 8.2) does prepare(), bind_param(), execute() and get_result() in one call. Values come as a list array matching the ? markers, and each is bound as a string.

open as a page

In PHP's mysqli, how does multi_query() differ from query(), and what must happen before the connection can run another statement?

level: seniorimportance: nice to knowfreq 18%

basics

~10 s

mysqli::query() runs one statement; multi_query() sends several separated by semicolons and returns after the first. Every result must be read with store_result() and next_result() before the connection accepts another statement.

open as a page