Databases with PDO

What PDO is

PDO (PHP Data Objects) is the database layer built into PHP: one set of classes, mainly PDO for the connection and PDOStatement for a query and its result, with a separate driver per database product (pdo_mysql, pdo_pgsql, pdo_sqlite, …). Code written against PDO looks the same for every database; the SQL dialect is the part that still differs. The general picture of how applications talk to databases (drivers, connection strings, pools, ORMs) is on databases from code; this page is the PHP side.

You will meet three database APIs in PHP code:

• PDO: many databases, object-oriented, the usual choice for new code and the layer underneath Doctrine and Laravel's database tools.
• mysqli: MySQL and MariaDB only, with an object-oriented and a procedural style. WordPress uses it (see mysqli and WordPress).
• mysql_* functions such as mysql_query(): removed in PHP 7.0. Code that still calls them cannot run on any supported PHP version and must be rewritten.

The examples on this page run on SQLite in memory (sqlite::memory:), so they need no database server. The SQL itself is explained in the Databases & SQL topic.

Connecting with a DSN

A connection is a new PDO(…) with up to four arguments: a DSN, a user name, a password and an array of options. The DSN (Data Source Name) is a string that starts with the driver name and a colon, followed by the driver's own settings, such as host, port and database name. It is PDO's form of a connection string.

<?php
// SQLite keeps this database in memory; it disappears when the script ends
$pdo = new PDO('sqlite::memory:', options: [
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
]);

echo $pdo->getAttribute(PDO::ATTR_DRIVER_NAME), "\n";
var_dump($pdo->getAttribute(PDO::ATTR_ERRMODE) === PDO::ERRMODE_EXCEPTION);

sqlite
bool(true)

Since PHP 8.0 the default error mode is PDO::ERRMODE_EXCEPTION: every failing query throws a PDOException. Older tutorials set it explicitly because the old default stayed silent; doing so is still harmless, and makes the intent clear. A connection that fails always throws, whatever the error mode. The DSNs for the common servers:

<?php
// MySQL or MariaDB: host, port, database and the connection character set
$dsn = 'mysql:host=127.0.0.1;port=3306;dbname=kestrel;charset=utf8mb4';

// The credentials come from the environment, not from the code
$pdo = new PDO($dsn, getenv('DB_USER') ?: '', getenv('DB_PASSWORD') ?: '', [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,  // the default since PHP 8.0
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,                   // real server-side prepares
]);

<?php
// PostgreSQL
$pdo = new PDO('pgsql:host=db.internal;port=5432;dbname=kestrel', $user, $password);

// SQLite: a file (the folder must be writable for PHP), or memory
$pdo = new PDO('sqlite:' . __DIR__ . '/../var/kestrel.sqlite');
$pdo = new PDO('sqlite::memory:');

For MySQL and MariaDB, always put charset=utf8mb4 in the DSN. utf8mb4 is full UTF-8 (MySQL's older utf8 cannot store characters such as emoji), and setting the character set in the DSN, rather than with a later SET NAMES query, lets PDO escape correctly when it emulates prepared statements. PHP 8.4 added driver-specific subclasses such as Pdo\Mysql and Pdo\Sqlite; plain PDO keeps working.

Where the credentials go

Database passwords do not belong in the source code or in Git. Read them from environment variables (set in the PHP-FPM pool, the container or the service unit) or from a configuration file outside the web root that is listed in .gitignore. Give the application its own database account with only the rights it needs (see least privilege for the application), and keep display_errors off in production so a failed connection never shows its details to a visitor (see production php.ini).

A PDO connection lives as long as the PDO object, normally until the end of the request. Create it once per request and pass it to the code that needs it, rather than connecting in every function.

Running SQL: exec and query

Two methods run SQL text directly. exec() runs a statement and returns the number of affected rows; use it for schema changes and fixed statements. query() runs a SELECT and returns a PDOStatement that you can loop over with foreach, one row at a time.

<?php
$pdo = new PDO('sqlite::memory:', options: [PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC]);

$pdo->exec('CREATE TABLE customers (
    id    INTEGER PRIMARY KEY,
    name  TEXT NOT NULL,
    email TEXT NOT NULL UNIQUE,
    city  TEXT NOT NULL
)');
$added = $pdo->exec("INSERT INTO customers (name, email, city) VALUES
    ('Fatima El Amrani', 'fatima@example.com', 'Den Haag'),
    ('Sean O''Brien',    'sean@example.com',   'Leiden'),
    ('Pieter de Graaf',  'pieter@example.com', 'Delft')");
echo "$added rows added\n";

foreach ($pdo->query('SELECT id, name, city FROM customers ORDER BY name') as $row) {
    echo "{$row['id']}  {$row['name']} ({$row['city']})\n";
}

3 rows added
1  Fatima El Amrani (Den Haag)
3  Pieter de Graaf (Delft)
2  Sean O'Brien (Leiden)

Both methods are only for SQL text that is entirely yours. The moment a value from outside (a form field, a URL parameter, a CSV file, even a value from your own database) has to go into the query, use a prepared statement.

Prepared statements

A prepared statement is a query sent to the database in two steps. First prepare() sends the SQL text with placeholders where the values go; the database parses it and returns a statement handle. Then execute() sends only the values. Because the values never become part of the SQL text, nothing in them can change the meaning of the query. This is PHP's form of a parameterized query, the standard defense against SQL injection.

The difference shows even with an honest value. A name with an apostrophe breaks the query that glues the value into the SQL text: the quote ends the string literal early. An attacker uses exactly that gap to add SQL of their own. The prepared statement handles the same value without trouble:

<?php
$pdo = new PDO('sqlite::memory:', options: [PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC]);
$pdo->exec('CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT, city TEXT)');
$pdo->exec("INSERT INTO customers (name, city) VALUES ('Fatima El Amrani', 'Den Haag'), ('Sean O''Brien', 'Leiden')");

$_GET = ['name' => "Sean O'Brien"];          // the search form, simulated

// Unsafe: the value becomes part of the SQL text, and its quote ends the string
try {
    $pdo->query("SELECT id, city FROM customers WHERE name = '{$_GET['name']}'");
} catch (PDOException $e) {
    echo 'Unsafe query failed: ', $e->getMessage(), "\n";
}

// Safe: the SQL text is fixed; the value is sent separately
$stmt = $pdo->prepare('SELECT id, city FROM customers WHERE name = :name');
$stmt->execute(['name' => $_GET['name'] ?? '']);
print_r($stmt->fetch());

Unsafe query failed: SQLSTATE[HY000]: General error: 1 near "Brien": syntax error
Array
(
    [id] => 2
    [city] => Leiden
)

Named and positional placeholders

A placeholder marks the spot in the SQL text where a value will be supplied. PDO accepts two styles: named placeholders such as :meter, matched by name to the keys of the array you pass to execute(), and positional placeholders ?, matched in order to a list. Use one style per statement, never both. Named placeholders read better when there are many values; positional ones are handy when the values are already in a list. A prepared statement can be executed many times with different values:

<?php
$pdo = new PDO('sqlite::memory:');
$pdo->exec('CREATE TABLE meter_readings (
    id INTEGER PRIMARY KEY, meter TEXT NOT NULL, read_on TEXT NOT NULL, kwh REAL NOT NULL
)');

// Prepared once, executed for every row
$insert = $pdo->prepare('INSERT INTO meter_readings (meter, read_on, kwh) VALUES (?, ?, ?)');
$readings = [
    ['KE-4471', '2026-10-01', 412.5],
    ['KE-4471', '2026-10-02', 418.0],
    ['KE-5120', '2026-10-02', 97.25],
];
foreach ($readings as $reading) {
    $insert->execute($reading);
}
echo 'Last id: ', $pdo->lastInsertId(), "\n";

$update = $pdo->prepare('UPDATE meter_readings SET kwh = :kwh WHERE meter = :meter AND read_on = :day');
$update->execute(['kwh' => 418.5, 'meter' => 'KE-4471', 'day' => '2026-10-02']);
echo $update->rowCount(), " row changed\n";

Last id: 3
1 row changed

lastInsertId() returns the key the database generated for the last inserted row (as a string), and rowCount() the number of rows an INSERT, UPDATE or DELETE changed. Do not rely on rowCount() for a SELECT: some drivers report it (MySQL, PostgreSQL), others such as SQLite return 0; count with SELECT COUNT(*) or count(fetchAll()) instead. The amounts here are REAL to keep the example short; money in a real table belongs in integer cents or DECIMAL (see money and floats).

Binding with types, and IN lists

Values passed to execute() are all sent as strings. That is fine for most comparisons, but when the type matters, bind each value with bindValue() and a type such as PDO::PARAM_INT, PDO::PARAM_BOOL or PDO::PARAM_NULL. A list of values for IN (…) needs one placeholder per value; build the placeholders, never the values, into the SQL text:

<?php
$pdo = new PDO('sqlite::memory:', options: [PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC]);
$pdo->exec('CREATE TABLE outages (ticket TEXT PRIMARY KEY, city TEXT, customers INTEGER)');
$pdo->exec("INSERT INTO outages VALUES ('INC-31870', 'Delft', 85), ('INC-31874', 'Leiden', 410),
    ('INC-31877', 'Den Haag', 1240), ('INC-31880', 'Gouda', 12)");

// bindValue() with an explicit type, here for LIMIT
$stmt = $pdo->prepare('SELECT ticket, customers FROM outages ORDER BY customers DESC LIMIT :n');
$stmt->bindValue('n', 2, PDO::PARAM_INT);
$stmt->execute();
foreach ($stmt as $row) {
    echo "{$row['ticket']}: {$row['customers']} customers\n";
}

// IN (...) needs one placeholder per value
$cities = ['Delft', 'Gouda', 'Leiden'];
$marks = implode(', ', array_fill(0, count($cities), '?'));     // "?, ?, ?"
$stmt = $pdo->prepare("SELECT ticket FROM outages WHERE city IN ($marks) ORDER BY ticket");
$stmt->execute($cities);
echo implode(', ', $stmt->fetchAll(PDO::FETCH_COLUMN)), "\n";

INC-31877: 1240 customers
INC-31874: 410 customers
INC-31870, INC-31874, INC-31880

Placeholders stand for values only. Table names, column names and keywords such as ASC cannot be parameters; when a request chooses them, map the choice to an allowlist (see names cannot be parameters).

Emulated prepares

Not every driver sends the two steps to the server. Emulated prepares are PDO's fallback: PDO keeps the SQL text, and at execute() it escapes and quotes each value itself and sends one ordinary query. The PDO MySQL driver uses emulation by default. With the character set in the DSN this is still safe against injection, but it behaves differently in small ways: a value bound without a type is quoted as a string, so LIMIT ? with execute([10]) becomes LIMIT '10', which MySQL rejects. Most projects therefore switch emulation off for MySQL with PDO::ATTR_EMULATE_PREPARES => false, as in the connection example above, so the server prepares the statement itself. The SQLite driver always prepares natively.

-- Emulated (the default for pdo_mysql): one query with the values filled in by PDO
SELECT ticket FROM outages WHERE city = 'Leiden' LIMIT '10'

-- Native (ATTR_EMULATE_PREPARES => false): prepare, then execute with the values separately
PREPARE: SELECT ticket FROM outages WHERE city = ? LIMIT ?
EXECUTE: 'Leiden', 10

Fetching rows

A fetch mode decides what shape each row takes in PHP. The default, PDO::FETCH_BOTH, gives every column twice, by name and by number, which doubles the data and confuses print_r() output. Set PDO::ATTR_DEFAULT_FETCH_MODE to PDO::FETCH_ASSOC when connecting, or pass a mode to a single call:

<?php
$pdo = new PDO('sqlite::memory:');
$pdo->exec('CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT, city TEXT, balance REAL)');
$pdo->exec("INSERT INTO customers (name, city, balance) VALUES
    ('Fatima El Amrani', 'Den Haag', -42.10), ('Sean O''Brien', 'Leiden', 0),
    ('Pieter de Graaf', 'Delft', 15.75)");

// One row as an associative array; false when there is none
$stmt = $pdo->prepare('SELECT name, balance FROM customers WHERE id = ?');
$stmt->execute([1]);
var_dump($stmt->fetch(PDO::FETCH_ASSOC));
$stmt->execute([99]);
var_dump($stmt->fetch(PDO::FETCH_ASSOC));

// All rows as objects
$rows = $pdo->query('SELECT name, city FROM customers ORDER BY name')->fetchAll(PDO::FETCH_OBJ);
echo $rows[0]->name, ' lives in ', $rows[0]->city, "\n";

// A single value
echo $pdo->query('SELECT COUNT(*) FROM customers WHERE balance < 0')->fetchColumn(), " in debt\n";

// Two columns as key => value
print_r($pdo->query('SELECT id, name FROM customers ORDER BY id')->fetchAll(PDO::FETCH_KEY_PAIR));

array(2) {
  ["name"]=>
  string(16) "Fatima El Amrani"
  ["balance"]=>
  float(-42.1)
}
bool(false)
Fatima El Amrani lives in Den Haag
1 in debt
Array
(
    [1] => Fatima El Amrani
    [2] => Sean O'Brien
    [3] => Pieter de Graaf
)

Check the result of fetch() before you use it: a customer ID from a URL may not exist, and indexing into false gives a warning and null, not an error. fetchAll() loads every row into memory at once, which is fine for a page of results but not for an export of a million meter readings; loop over the statement with foreach or fetch() instead.

Since PHP 8.1, integer and float columns arrive as PHP int and float with both SQLite and MySQL (float(-42.1) above; for MySQL, 8.1 extended this to emulated prepares, native prepares already did it). MySQL DECIMAL values still arrive as strings. Older code written for PHP 7 often received numbers as strings and casts them; with strict_types such differences surface as TypeErrors.

Transactions

Some changes only make sense together: an invoice and its lines, a payment and the new balance. A database transaction makes them all-or-nothing. In PDO, beginTransaction() starts one, commit() makes the changes permanent, and rollBack() undoes everything since the start. The usual shape is a try block that ends with commit() and a catch that rolls back and rethrows:

<?php
$pdo = new PDO('sqlite::memory:');
$pdo->exec('CREATE TABLE invoices (id INTEGER PRIMARY KEY, customer_id INTEGER NOT NULL, total REAL NOT NULL)');
$pdo->exec('CREATE TABLE invoice_lines (
    invoice_id INTEGER NOT NULL, description TEXT NOT NULL, amount REAL NOT NULL CHECK (amount >= 0)
)');

function create_invoice(PDO $pdo, int $customerId, array $lines): int
{
    $pdo->beginTransaction();
    try {
        $pdo->prepare('INSERT INTO invoices (customer_id, total) VALUES (?, ?)')
            ->execute([$customerId, array_sum(array_column($lines, 'amount'))]);
        $invoiceId = (int) $pdo->lastInsertId();

        $line = $pdo->prepare('INSERT INTO invoice_lines (invoice_id, description, amount) VALUES (?, ?, ?)');
        foreach ($lines as $l) {
            $line->execute([$invoiceId, $l['description'], $l['amount']]);
        }
        $pdo->commit();
        return $invoiceId;
    } catch (Throwable $e) {
        $pdo->rollBack();          // undo the invoice and any lines already added
        throw $e;
    }
}

echo 'Invoice ', create_invoice($pdo, 7, [
    ['description' => 'Electricity, September', 'amount' => 96.40],
    ['description' => 'Standing charge', 'amount' => 6.25],
]), " created\n";

try {
    create_invoice($pdo, 8, [
        ['description' => 'Electricity, September', 'amount' => 54.10],
        ['description' => 'Correction', 'amount' => -12.00],     // violates the CHECK
    ]);
} catch (PDOException $e) {
    echo "Second invoice rolled back\n";
}

echo $pdo->query('SELECT COUNT(*) FROM invoices')->fetchColumn(), ' invoice(s), ';
echo $pdo->query('SELECT COUNT(*) FROM invoice_lines')->fetchColumn(), " line(s)\n";

Invoice 1 created
Second invoice rolled back
1 invoice(s), 2 line(s)

The second invoice row was inserted before its second line failed the CHECK constraint; the rollback removed it again, so the tables hold exactly one complete invoice. Catch Throwable rather than only PDOException in the rollback block, so a PHP error halfway also undoes the database work. If a script ends with a transaction still open, PDO rolls it back when the connection closes; changes are only kept after commit(). Keep transactions short (see keep transactions short): no waiting for an API or a user inside one.

Handling database errors

A failing query throws a PDOException. Its getCode() returns the five-character SQLSTATE code as a string: class 23 means a constraint violation (a duplicate unique value, a missing foreign key), class 42 a syntax error or a missing table. The property errorInfo adds the driver's own error number and message. Decide per error whether the user can do something about it:

<?php
$pdo = new PDO('sqlite::memory:');
$pdo->exec('CREATE TABLE customers (id INTEGER PRIMARY KEY, email TEXT NOT NULL UNIQUE)');
$pdo->exec("INSERT INTO customers (email) VALUES ('fatima@example.com')");

function register(PDO $pdo, string $email): string
{
    try {
        $pdo->prepare('INSERT INTO customers (email) VALUES (?)')->execute([$email]);
        return 'Account created.';
    } catch (PDOException $e) {
        if ($e->getCode() === '23000') {          // SQLSTATE class 23: constraint violation
            return 'This email address is already registered.';
        }
        error_log('Registration failed: ' . $e->getMessage());   // details to the log only
        return 'Something went wrong. Please try again later.';
    }
}

echo register($pdo, 'sean@example.com'), "\n";
echo register($pdo, 'fatima@example.com'), "\n";

Account created.
This email address is already registered.

The full message (with SQL fragments, table names and sometimes the host name) goes to the error log, never to the page: it helps an attacker more than the user. An exception you do not catch at all ends in PHP's normal error handling, which in production logs it and shows a general error page. Do not wrap every query in its own try/catch; catch where you can respond sensibly, as in register() above, and let the rest reach one handler at the top (see uncaught exceptions).

mysqli, WordPress and ORMs

Maintenance work often means reading code that does not use PDO. The rule is the same everywhere: SQL text with placeholders, values passed separately.

<?php
// mysqli: MySQL and MariaDB only
$stmt = $mysqli->prepare('SELECT id, city FROM customers WHERE email = ?');
$stmt->bind_param('s', $email);          // 's' = string, 'i' = integer, 'd' = float
$stmt->execute();
$customer = $stmt->get_result()->fetch_assoc();

// PHP 8.2 and later: prepare, bind and execute in one call
$customer = $mysqli->execute_query('SELECT id, city FROM customers WHERE email = ?', [$email])->fetch_assoc();

// WordPress: $wpdb->prepare() with %s, %d and %f placeholders
$customer = $wpdb->get_row(
    $wpdb->prepare("SELECT id, city FROM {$wpdb->prefix}customers WHERE email = %s", $email)
);

WordPress requires the mysqli extension and wraps it in the global $wpdb object; plugins should use $wpdb->prepare() for every query with a variable, and a plugin that builds SQL by concatenation is a classic source of SQL injection reports. Frameworks add a layer on top: Laravel's query builder and Eloquent, and Doctrine in Symfony, generate prepared statements for you (see ORMs), while their "raw" methods are as dangerous as query() with a concatenated string.