Databases with PDO and prepared statements

DSN and connection options, bound parameters, transactions, fetch modes, mapping rows to objects, and avoiding N+1 queries.

Connecting and preparing

<?php
declare(strict_types=1);

$dsn = "mysql:host=127.0.0.1;dbname=shop;charset=utf8mb4";
$pdo = new PDO($dsn, $user, $pass, [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,
]);

// one parameter, named or positional
$stmt = $pdo->prepare("SELECT id, email FROM users WHERE email = :email AND active = 1");
$stmt->execute(["email" => $email]);
$user = $stmt->fetch();

// several rows
$stmt = $pdo->prepare("SELECT id, total FROM orders WHERE customer_id = ? AND created_at > ?");
$stmt->execute([$customerId, $since]);
$orders = $stmt->fetchAll();

// writes: the id comes back from the connection, not from the statement
$insert = $pdo->prepare("INSERT INTO orders (customer_id, total) VALUES (?, ?)");
$insert->execute([$customerId, $total]);
$orderId = (int) $pdo->lastInsertId();

• charset=utf8mb4 in the DSN is required for full Unicode; without it emoji and some scripts are mangled or rejected.
• Bound parameters are never interpolated into SQL text, so quoting and injection stop being your problem.
• A parameter cannot stand in for a table or column name. Whitelist those identifiers in code instead.
• Passing false for EMULATE_PREPARES makes the server do the parsing, which gives real types and better errors.

Transactions and fetch modes

<?php
try {
    $pdo->beginTransaction();
    $pdo->prepare("UPDATE accounts SET balance = balance - ? WHERE id = ?")
        ->execute([$amount, $fromId]);
    $pdo->prepare("UPDATE accounts SET balance = balance + ? WHERE id = ?")
        ->execute([$amount, $toId]);
    $pdo->commit();
} catch (Throwable $e) {
    $pdo->rollBack();       // safe even if the transaction already ended
    throw $e;
}

$stmt = $pdo->query("SELECT id, email, name FROM users");
$objects = $stmt->fetchAll(PDO::FETCH_CLASS, User::class);   // maps to objects
$pairs   = $stmt->fetchAll(PDO::FETCH_KEY_PAIR);             // id => email
$ids     = $stmt->fetchAll(PDO::FETCH_COLUMN, 0);

Worked example: killing an N+1 query

<?php
// slow: one query per row
foreach ($orders as $order) {
    $order->customer = $pdo->prepare("SELECT * FROM customers WHERE id = ?")
        ->execute([$order->customerId]);
}

// fast: one extra query for the whole page
$ids  = array_values(array_unique(array_column($orders, "customerId")));
$in   = implode(",", array_fill(0, count($ids), "?"));
$stmt = $pdo->prepare("SELECT * FROM customers WHERE id IN ($in)");
$stmt->execute($ids);
$byId = $stmt->fetchAll(PDO::FETCH_UNIQUE | PDO::FETCH_ASSOC);

foreach ($orders as $order) {
    $order->customer = $byId[$order->customerId] ?? null;
}

Chunk the IN list when it can grow to thousands of ids: databases limit the number of placeholders, and array_chunk() plus a loop over chunks keeps the query predictable. Add an index on the joined column so each lookup is a seek rather than a scan.

FAQ

Related

Files, streams and the filesystem Composer, dependencies and coding standards

Last refreshed 2026-09-18.