Advanced PDO

Reviewed & published by Brayan K

By the end of this lesson you'll pick the right fetch mode, reuse prepared statements in loops, and wrap multi-step writes in transactions that either fully commit or cleanly roll back — the skills behind every safe, fast database app.

Part of the free PHP course at LearnCodingFast — hands-on lessons with worked examples and the output they print, plus practice exercises and a quick quiz.

What You'll Learn in This Lesson

• Choose between FETCH_ASSOC, FETCH_OBJ and FETCH_CLASS for each query
• Reuse one prepared statement across a loop for speed and safety
• Wrap multi-step writes in a transaction with beginTransaction/commit/rollBack
• Tell bindValue from bindParam and force types with PDO::PARAM constants
• Read lastInsertId() and rowCount() after a write
• Build a safe IN (...) clause with dynamic placeholders

1️⃣ Fetch Modes: ASSOC, OBJ & CLASS

A fetch mode decides the shape of each row PDO hands back. PDO::FETCH_ASSOC gives you an associative array you read by column name ($row['name']) — the everyday choice. PDO::FETCH_OBJ gives a plain object you read with arrow syntax ($row->name). The powerful one is PDO::FETCH_CLASS: it pours each row straight into an object of your class, so the data arrives with real methods attached. That last mode is the seed of every ORM.

<?php
// Every example here uses an in-memory SQLite database so it runs anywhere.
// The PDO API is IDENTICAL for MySQL/PostgreSQL — only the connection string changes.
$pdo = new PDO("sqlite::memory:");
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); // throw on errors

$pdo->exec("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, age INTEGER)");
$pdo->exec("INSERT INTO users (name, age) VALUES ('Alice', 28), ('Bob', 35)");

// A "fetch mode" decides the SHAPE of each row you get back.

// 1) FETCH_ASSOC — each row is an associative array, keyed by column name.
$row = $pdo->query("SELECT * FROM users WHERE name = 'Alice'")
           ->fetch(PDO::FETCH_ASSOC);
echo "ASSOC:  " . $row['name'] . " is " . $row['age'] . "\n"; // use ['key']

// 2) FETCH_OBJ — each row is a plain object; read columns as ->property.
$row = $pdo->query("SELECT * FROM users WHERE name = 'Bob'")
           ->fetch(PDO::FETCH_OBJ);
echo "OBJ:    " . $row->name . " is " . $row->age . "\n";       // use ->prop

// 3) FETCH_CLASS — map each row straight into objects of YOUR class.
class User {
    public string $name;
    public int $age;
    public function greet(): string {
        return "Hi, I'm {$this->name} ({$this->age})";
    }
}
$people = $pdo->query("SELECT name, age FROM users ORDER BY name")
              ->fetchAll(PDO::FETCH_CLASS, User::class); // array of User objects
foreach ($people as $person) {
    echo "CLASS:  " . $person->greet() . "\n"; // real methods, not just data
}

Set a default once with $pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC) and you won't have to pass the mode on every call. You can still override it per query when one row needs a different shape.

2️⃣ Reusing a Prepared Statement in a Loop

Calling prepare() asks the database to parse and plan the SQL. Do it once outside the loop, then call execute() with fresh values inside the loop — the plan is reused and each value is still safely escaped. Two handy facts after a write: lastInsertId() returns the auto-increment id of the most recent INSERT, and rowCount() tells you how many rows a write touched.

<?php
$pdo = new PDO("sqlite::memory:");
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC); // default shape
$pdo->exec("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, email TEXT, age INTEGER)");

$people = [
    ["Alice",   "[email protected]",   28],
    ["Bob",     "[email protected]",     35],
    ["Charlie", "[email protected]", 22],
];

// Prepare ONCE, execute MANY. The database parses the SQL a single time and
// reuses the plan for every row — faster and safe from SQL injection.
$stmt = $pdo->prepare("INSERT INTO users (name, email, age) VALUES (?, ?, ?)");
foreach ($people as $p) {
    $stmt->execute($p);                 // re-run the SAME statement with new values
}

// lastInsertId() returns the auto-increment id of the most recent INSERT.
echo "Last inserted id: " . $pdo->lastInsertId() . "\n"; // 3 (Charlie)

// rowCount() tells you how many rows a write affected.
$update = $pdo->prepare("UPDATE users SET age = age + 1 WHERE age < :limit");
$update->execute([":limit" => 30]);
echo "Rows updated: " . $update->rowCount() . "\n"; // Alice + Charlie = 2

echo "\nEveryone:\n";
foreach ($pdo->query("SELECT name, age FROM users ORDER BY id") as $row) {
    echo "  {$row['name']} ({$row['age']})\n"; // FETCH_ASSOC default
}

3️⃣ bindValue vs bindParam (and Types)

Both attach a value to a placeholder, but they differ in when the value is read. bindValue() takes a snapshot of the value right now. bindParam() binds a variable by reference, so PDO reads its value at execute() time — change the variable in a loop and the query changes with it. The optional third argument forces a type: PDO::PARAM_INT for integers, PDO::PARAM_STR for strings, PDO::PARAM_BOOL for booleans.

<?php
$pdo = new PDO("sqlite::memory:");
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$pdo->exec("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, age INTEGER)");
$pdo->exec("INSERT INTO users (name, age) VALUES ('Alice', 28), ('Bob', 35), ('Cara', 41)");

// bindValue — binds the value AS IT IS RIGHT NOW. Use it for plain values.
// PDO::PARAM_INT forces the parameter to be treated as an integer.
$stmt = $pdo->prepare("SELECT name, age FROM users WHERE age >= :min ORDER BY age");
$stmt->bindValue(":min", 30, PDO::PARAM_INT); // snapshot: 30, as an int
$stmt->execute();
echo "Aged 30+:\n";
foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $r) {
    echo "  {$r['name']} ({$r['age']})\n";
}

// bindParam — binds a VARIABLE BY REFERENCE. The value is read at execute()
// time, so the SAME bound statement can be re-run as the variable changes.
$minAge = 0;
$stmt = $pdo->prepare("SELECT COUNT(*) AS n FROM users WHERE age >= :min");
$stmt->bindParam(":min", $minAge, PDO::PARAM_INT); // bind the variable, not its value
foreach ([28, 35, 41] as $minAge) {                // changing $minAge changes the query
    $stmt->execute();
    $n = $stmt->fetch(PDO::FETCH_ASSOC)['n'];
    echo "age >= {$minAge}: {$n}\n";
}

4️⃣ Transactions: All or Nothing

A transaction bundles several writes into one atomic unit: every query commits together, or none of them do. You open it with beginTransaction(), do the work inside a try block, commit() on success, and rollBack() in catch so any failure cleanly undoes everything. This is essential for money transfers, order processing, or any multi-step update where a half-finished result would corrupt your data.

<?php
// A transaction groups queries into ONE atomic operation: either every query
// commits, or rollBack() undoes them all. Perfect for money transfers.
$pdo = new PDO("sqlite::memory:");
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$pdo->exec("CREATE TABLE accounts (name TEXT PRIMARY KEY, balance INTEGER)");
$seed = $pdo->prepare("INSERT INTO accounts (name, balance) VALUES (?, ?)");
foreach (["Alice" => 1000, "Bob" => 500] as $name => $balance) {
    $seed->execute([$name, $balance]);
}

function transfer(PDO $pdo, string $from, string $to, int $amount): void
{
    $pdo->beginTransaction();                 // open the transaction
    try {
        $debit = $pdo->prepare("UPDATE accounts SET balance = balance - ? WHERE name = ?");
        $debit->execute([$amount, $from]);

        // Check the rule INSIDE the transaction, before committing.
        $check = $pdo->prepare("SELECT balance FROM accounts WHERE name = ?");
        $check->execute([$from]);
        if ($check->fetchColumn() < 0) {
            throw new RuntimeException("Insufficient funds for $from");
        }

        $credit = $pdo->prepare("UPDATE accounts SET balance = balance + ? WHERE name = ?");
        $credit->execute([$amount, $to]);

        $pdo->commit();                       // make all changes permanent
        echo "OK:   \$$amount $from -> $to\n";
    } catch (Throwable $e) {
        $pdo->rollBack();                     // undo EVERYTHING on any failure
        echo "FAIL: {$e->getMessage()} (rolled back)\n";
    }
}

transfer($pdo, "Alice", "Bob", 200);   // succeeds -> committed
transfer($pdo, "Bob", "Alice", 9999);  // overdraws -> rolled back

echo "\nFinal balances:\n";
foreach ($pdo->query("SELECT name, balance FROM accounts ORDER BY name") as $row) {
    printf("  %-6s \$%d\n", $row["name"], $row["balance"]);
}

5️⃣ A Safe IN (...) Clause

You can't bind a whole array to one placeholder, and you must never glue user values into the SQL string yourself — that's a classic injection hole. Instead build one ? per value, splice them into the IN (...), and pass the matching array to execute(). The placeholder count always lines up with the value count, and PDO escapes every value for you.

<?php
$pdo = new PDO("sqlite::memory:");
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$pdo->exec("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)");
$pdo->exec("INSERT INTO users (name) VALUES ('Alice'), ('Bob'), ('Cara'), ('Dan')");

// You CANNOT bind an array to a single placeholder. Build one ? per value,
// then pass the matching array — the count always lines up automatically.
$wanted = ["Alice", "Cara", "Dan"];

// str_repeat("?,", 3) = "?,?,?," — trim the trailing comma to get "?,?,?".
$placeholders = rtrim(str_repeat("?,", count($wanted)), ",");
$sql = "SELECT name FROM users WHERE name IN ($placeholders) ORDER BY name";

$stmt = $pdo->prepare($sql);   // SELECT ... WHERE name IN (?,?,?)
$stmt->execute($wanted);        // one value per placeholder, safely escaped

echo "Matched:\n";
foreach ($stmt->fetchAll(PDO::FETCH_COLUMN) as $name) { // flat list of one column
    echo "  $name\n";
}

6️⃣ Your Turn

Now you drive. The script below is almost complete — fill in each ___ using the 👉 hint, then run it and check it against the Output panel. First, fetch a row as an object.

<?php
// 🎯 YOUR TURN — fill in each blank marked ___ , then run it.
$pdo = new PDO("sqlite::memory:");
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$pdo->exec("CREATE TABLE pets (name TEXT, kind TEXT)");
$pdo->exec("INSERT INTO pets VALUES ('Rex', 'dog'), ('Milo', 'cat')");

// 1) Fetch ONE row as an OBJECT so you can read columns with ->
$stmt = $pdo->query("SELECT * FROM pets WHERE name = 'Rex'");
$pet  = $stmt->fetch(___);          // 👉 use the fetch mode for objects: PDO::FETCH_OBJ

// 2) Print the pet using the -> property syntax (NOT ['name'])
echo $pet->name . " is a " . ___ . "\n";   // 👉 read the kind column: $pet->kind

// ✅ Expected output:
//    Rex is a dog
?>

One more, and it's the big one: finish a transaction so the transfer is atomic. Add the line that opens it and the line that makes it permanent.

<?php
// 🎯 YOUR TURN — a transfer is half-written. Finish the transaction.
$pdo = new PDO("sqlite::memory:");
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$pdo->exec("CREATE TABLE accounts (name TEXT, balance INTEGER)");
$pdo->exec("INSERT INTO accounts VALUES ('Alice', 100), ('Bob', 0)");

___;                               // 👉 1) open the transaction: $pdo->beginTransaction()
try {
    $pdo->prepare("UPDATE accounts SET balance = balance - ? WHERE name = ?")
        ->execute([40, "Alice"]);
    $pdo->prepare("UPDATE accounts SET balance = balance + ? WHERE name = ?")
        ->execute([40, "Bob"]);
    ___;                           // 👉 2) make it permanent: $pdo->commit()
    echo "Transfer committed\n";
} catch (Throwable $e) {
    $pdo->rollBack();              // 👉 3) on error, undo everything (already done for you)
    echo "Rolled back\n";
}

foreach ($pdo->query("SELECT name, balance FROM accounts ORDER BY name") as $r) {
    echo "  {$r['name']}: {$r['balance']}\n";
}

// ✅ Expected output:
//    Transfer committed
//      Alice: 60
//      Bob: 40
?>

Common Errors (and the fix)

• A multi-step write leaves orphaned data — you inserted an order and its items without a transaction, and the items failed. Wrap related writes in beginTransaction() … commit() with a rollBack() in catch, so a failure on step two undoes step one too.
• Injection still possible despite "prepared" statements — you left PDO::ATTR_EMULATE_PREPARES on and built the SQL by concatenating values. Emulated prepares quote on the client, not the server. Send real prepared statements with $pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false) and always pass values as parameters, never by string-joining.
• "SQLSTATE... number of bound variables does not match" on an IN (...) query — you hard-coded a fixed set of ? but passed a different-length array, or you tried to bind the array to one placeholder. Generate the placeholders dynamically from the array (see section 5) so the counts always match.
• "Trying to access array offset on value of type object" (or the reverse) — fetch-mode confusion. With FETCH_OBJ you must use $row->name; with FETCH_ASSOC you must use $row['name']. Match the access syntax to the fetch mode you chose.
• "There is no active transaction" on rollBack() — your commit() already closed it, or beginTransaction() never ran. Only call rollBack() while a transaction is open; guard with if ($pdo->inTransaction()) if a path is uncertain.

Pro Tips

• 💡 Wrap big batches in one transaction. Inserting 1,000+ rows inside a single transaction can be 50–100× faster than committing each row separately.
• 💡 Turn off emulated prepares with PDO::ATTR_EMULATE_PREPARES => false so the server parses your SQL — better security and accurate parameter types.
• 💡 Set a default fetch mode of FETCH_ASSOC in the constructor; it's clearer than the default FETCH_BOTH, which returns every column twice.

📋 Quick Reference — Advanced PDO

Mini-Challenge: Bulk Insert + Filtered Read

No code is filled in this time — just a brief and an outline. Write it yourself, run it on onecompiler.com/php or your own machine, then check your result against the expected output in the comments. This is exactly the prepare-loop-commit pattern you'll reach for on real data.

<?php
// 🎯 MINI-CHALLENGE: bulk insert + filtered read
// No logic is filled in — work from the steps, then run it.
//
// Setup (copy this as-is):
$pdo = new PDO("sqlite::memory:");
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
$pdo->exec("CREATE TABLE scores (player TEXT, points INTEGER)");

$rows = [["Ana", 50], ["Ben", 90], ["Cleo", 70]];

// 1. prepare ONE INSERT statement: "INSERT INTO scores VALUES (?, ?)"
// 2. wrap the inserts in a transaction (beginTransaction / commit)
// 3. loop over $rows and execute() the prepared statement for each
// 4. SELECT player, points WHERE points >= 70 ORDER BY points DESC
// 5. echo each surviving row as "player: points"
//
// ✅ Expected output:
//    Ben: 90
//    Cleo: 70

// your code here
?>

🎉 Lesson Complete!

• ✅ Fetch modes set a row's shape: FETCH_ASSOC (array), FETCH_OBJ (object), FETCH_CLASS (your class, with methods)
• ✅ Prepare once, execute many in a loop — reuse the plan, stay injection-safe
• ✅ Transactions with beginTransaction/commit/rollBack in try/catch make multi-step writes all-or-nothing
• ✅ bindValue snapshots a value; bindParam binds a variable by reference; both can force a PDO::PARAM type
• ✅ lastInsertId() and rowCount() report on the last write
• ✅ Build a safe IN (...) with one dynamic placeholder per value

Practice quiz

Continue this course

• Previous: Middleware Architecture & Request Pipelines
• Next: Query Builders & ORM Concepts (Doctrine/Eloquent Style) — Map database tables to PHP objects with ORM concepts and query builders
• Quick reference: PHP cheat sheet
• From the blog: PHP Security Best Practices: A Complete Guide

Frequently asked questions

What is the difference between bindValue and bindParam?

bindValue binds a value as it is at that moment — a snapshot. bindParam binds a variable by reference, so PDO reads the variable's current value at execute() time, not when you bind it. That makes bindParam useful when you re-run the same statement in a loop and change the variable between runs. For most one-off queries bindValue (or just passing an array to execute()) is simpler and clearer. bindParam is also required for output parameters from stored procedures.

Why should I prepare a statement once and reuse it in a loop?

Each prepare() asks the database to parse and plan the SQL. If you prepare inside the loop you pay that cost on every iteration; if you prepare once outside the loop and only call execute() inside, the database reuses the plan. Wrapping the loop in a single transaction makes it dramatically faster still — often 50 to 100 times faster for large batches — because the database commits once at the end instead of after every row.

How do I safely use an IN (...) clause with PDO?

You cannot bind a whole array to one placeholder, and you must never paste the values into the SQL string yourself. Build one placeholder per value with rtrim(str_repeat('?,', count($values)), ','), splice that into IN (...), prepare the query, then pass the array straight to execute(). The number of placeholders always matches the number of values, and every value is escaped by PDO, so there is no injection risk.

Which fetch mode should I use — ASSOC, OBJ, or CLASS?

Use FETCH_ASSOC for plain data you loop over and read by column name ($row['name']); it is the most common choice. Use FETCH_OBJ when you prefer object syntax ($row->name) but do not need methods. Use FETCH_CLASS when you want each row hydrated into a real object of your own class so it carries behaviour (methods), which is the foundation of how ORMs map tables to objects. Avoid the default FETCH_BOTH, which returns every column twice (numeric and string keys) and wastes memory.

Do I always have to call rollBack() myself?

You call rollBack() in your catch block to undo a transaction when something goes wrong. If the script simply ends or the connection closes before commit(), the database discards the open transaction for you, so uncommitted changes are not saved. The danger is the opposite: forgetting to commit(), which silently throws away work that looked like it succeeded. Always commit() on the happy path and rollBack() on failure.

Related lessons

• Databases & PDO