🔄 Lesson 20: PDO — CRUD, Transactions & Fetch Styles

CRUD Overview

CRUD stands for Create, Read, Update, Delete — the four fundamental operations for any database-driven application. In Lesson 19 you mastered the R (Read). This lesson covers the remaining three.

Every write operation follows the same three-step pattern you already know: prepare → execute → check result. The only new pieces are lastInsertId() for inserts and rowCount() for updates and deletes.

INSERT — Creating Records

Basic INSERT

<?php
require_once "Database.php";
$pdo = Database::getConnection();

// Insert a single row
$stmt = $pdo->prepare(
    "INSERT INTO users (name, email, role) VALUES (:name, :email, :role)"
);

$stmt->execute([
    "name"  => "Diana Prince",
    "email" => "diana@example.com",
    "role"  => "editor",
]);

echo "User created!";

Getting the New ID with lastInsertId()

After an INSERT into a table with an AUTO_INCREMENT primary key, you almost always need the new row's ID — to redirect to it, log it, or insert related data.

<?php
$stmt = $pdo->prepare(
    "INSERT INTO users (name, email) VALUES (:name, :email)"
);
$stmt->execute([
    "name"  => "Clark Kent",
    "email" => "clark@dailyplanet.com",
]);

// Get the AUTO_INCREMENT ID of the row we just inserted
$newId = $pdo->lastInsertId();

echo "New user ID: $newId";
// → "New user ID: 47"

// Common pattern: redirect to the new record
// header("Location: user.php?id=$newId");
// exit;

Inserting Multiple Rows

<?php
// Prepare once, execute many times — efficient batch insert
$stmt = $pdo->prepare(
    "INSERT INTO products (name, price, category) VALUES (:name, :price, :category)"
);

$products = [
    ["name" => "Wireless Mouse",    "price" => 29.99, "category" => "Electronics"],
    ["name" => "USB-C Hub",         "price" => 49.99, "category" => "Electronics"],
    ["name" => "Standing Desk Mat", "price" => 39.99, "category" => "Office"],
    ["name" => "Mechanical Keyboard","price" => 89.99, "category" => "Electronics"],
];

$insertedIds = [];
foreach ($products as $product) {
    $stmt->execute($product);
    $insertedIds[] = $pdo->lastInsertId();
}

echo count($insertedIds) . " products inserted.\n";
echo "IDs: " . implode(", ", $insertedIds);
// → "4 products inserted."
// → "IDs: 1, 2, 3, 4"

INSERT with ON DUPLICATE KEY UPDATE

Sometimes you want to insert a row, but if it already exists (based on a unique key), update it instead. MySQL's ON DUPLICATE KEY UPDATE handles this in a single query — no need to check-then-insert.

<?php
// "Upsert" — insert or update if the email already exists
// Assumes: UNIQUE KEY on the email column
$stmt = $pdo->prepare("
    INSERT INTO users (name, email, role)
    VALUES (:name, :email, :role)
    ON DUPLICATE KEY UPDATE
        name = VALUES(name),
        role = VALUES(role)
");

$stmt->execute([
    "name"  => "Diana Prince",
    "email" => "diana@example.com",  // If this email exists → UPDATE
    "role"  => "admin",
]);

// rowCount() returns:
// 1 → new row inserted
// 2 → existing row updated
$action = $stmt->rowCount() === 1 ? "inserted" : "updated";
echo "User $action.";

UPDATE — Modifying Records

Basic UPDATE

<?php
// Update a single record by ID
$stmt = $pdo->prepare(
    "UPDATE users SET name = :name, email = :email WHERE id = :id"
);

$stmt->execute([
    "name"  => "Diana of Themyscira",
    "email" => "diana@amazons.org",
    "id"    => 42,
]);

// How many rows were actually changed?
$affected = $stmt->rowCount();
echo "$affected row(s) updated.";

rowCount() — Checking Affected Rows

After an UPDATE or DELETE, rowCount() tells you how many rows were actually modified. This is essential for giving the user meaningful feedback.

<?php
$stmt = $pdo->prepare("UPDATE users SET role = :role WHERE id = :id");
$stmt->execute(["role" => "admin", "id" => 42]);

$affected = $stmt->rowCount();

if ($affected === 0) {
    echo "No changes made. The user may not exist or already has this role.";
} else {
    echo "User updated successfully.";
}

Conditional Updates

<?php
// Update multiple columns conditionally
$stmt = $pdo->prepare("
    UPDATE products
    SET price = :price, updated_at = NOW()
    WHERE id = :id AND price != :price
");
$stmt->execute(["price" => 34.99, "id" => 7]);

// Bulk update — deactivate users who haven't logged in this year
$stmt = $pdo->prepare(
    "UPDATE users SET active = 0 WHERE last_login < :cutoff AND active = 1"
);
$stmt->execute(["cutoff" => "2026-01-01"]);

$deactivated = $stmt->rowCount();
echo "$deactivated inactive accounts deactivated.";

UPDATE with LIMIT

<?php
// Process a batch of pending orders (useful for cron jobs)
$stmt = $pdo->prepare("
    UPDATE orders
    SET status = 'processing', processed_at = NOW()
    WHERE status = 'pending'
    ORDER BY created_at ASC
    LIMIT :batch_size
");
$stmt->bindValue(":batch_size", 50, PDO::PARAM_INT);
$stmt->execute();

echo $stmt->rowCount() . " orders moved to processing.";

DELETE — Removing Records

Basic DELETE

<?php
// Delete a single record by ID
$stmt = $pdo->prepare("DELETE FROM users WHERE id = :id");
$stmt->execute(["id" => 42]);

$deleted = $stmt->rowCount();

if ($deleted === 0) {
    echo "User not found — nothing deleted.";
} else {
    echo "User deleted successfully.";
}

Delete with Confirmation Pattern

In real applications, you'll often want to verify the record exists before deleting — and potentially return its data so you can display a confirmation message.

<?php
function deleteUser(PDO $pdo, int $id): ?array {
    // Step 1: Fetch the user first (so we can confirm what was deleted)
    $stmt = $pdo->prepare("SELECT id, name, email FROM users WHERE id = ?");
    $stmt->execute([$id]);
    $user = $stmt->fetch();

    if (!$user) {
        return null; // User doesn't exist
    }

    // Step 2: Delete the user
    $stmt = $pdo->prepare("DELETE FROM users WHERE id = ?");
    $stmt->execute([$id]);

    return $user; // Return the deleted user's data
}

// Usage
$deleted = deleteUser($pdo, 42);

if ($deleted) {
    echo "Deleted user: {$deleted['name']} ({$deleted['email']})";
} else {
    echo "User not found.";
}

Soft Delete vs. Hard Delete

Many applications don't actually delete records — they mark them as deleted instead. This preserves data for auditing, recovery, and referential integrity.

<?php
// Hard delete — row is gone forever
$pdo->prepare("DELETE FROM users WHERE id = ?")->execute([$id]);

// Soft delete — row is hidden but still in the database
$pdo->prepare(
    "UPDATE users SET deleted_at = NOW() WHERE id = ? AND deleted_at IS NULL"
)->execute([$id]);

// When querying, exclude soft-deleted records:
$stmt = $pdo->query("SELECT * FROM users WHERE deleted_at IS NULL ORDER BY name");
$activeUsers = $stmt->fetchAll();

// To "restore" a soft-deleted record:
$pdo->prepare(
    "UPDATE users SET deleted_at = NULL WHERE id = ?"
)->execute([$id]);

Cleanup Queries

<?php
// Delete expired sessions
$stmt = $pdo->prepare("DELETE FROM sessions WHERE expires_at < NOW()");
$stmt->execute();
echo $stmt->rowCount() . " expired sessions removed.";

// Delete old logs (keep last 90 days)
$stmt = $pdo->prepare(
    "DELETE FROM logs WHERE created_at < DATE_SUB(NOW(), INTERVAL :days DAY)"
);
$stmt->bindValue(":days", 90, PDO::PARAM_INT);
$stmt->execute();
echo $stmt->rowCount() . " old log entries purged.";

// TRUNCATE — delete ALL rows instantly (resets AUTO_INCREMENT)
// Use with extreme caution — no WHERE clause, no undo
$pdo->exec("TRUNCATE TABLE temp_imports");

Transactions

A transaction groups multiple SQL statements into a single all-or-nothing operation. Either every statement succeeds and the changes are saved (commit), or any failure rolls back all changes as if nothing happened (rollback). This guarantees data consistency.

Why Transactions Matter

Without transactions, if your code inserts an order but crashes before updating the inventory, you have an order with no stock deduction — your data is inconsistent. Transactions prevent this class of bugs entirely.

Basic Transaction

<?php
try {
    // Step 1: Start the transaction
    $pdo->beginTransaction();

    // Step 2: Run multiple statements
    $stmt = $pdo->prepare(
        "INSERT INTO orders (user_id, total) VALUES (:user_id, :total)"
    );
    $stmt->execute(["user_id" => 1, "total" => 59.98]);
    $orderId = $pdo->lastInsertId();

    $stmt = $pdo->prepare(
        "INSERT INTO order_items (order_id, product_id, quantity, price)
         VALUES (:order_id, :product_id, :quantity, :price)"
    );
    $stmt->execute([
        "order_id"   => $orderId,
        "product_id" => 101,
        "quantity"    => 2,
        "price"       => 29.99,
    ]);

    $stmt = $pdo->prepare(
        "UPDATE products SET stock = stock - :qty WHERE id = :id AND stock >= :qty"
    );
    $stmt->execute(["qty" => 2, "id" => 101]);

    // Check that the stock was actually deducted
    if ($stmt->rowCount() === 0) {
        throw new RuntimeException("Insufficient stock for product 101");
    }

    // Step 3: Everything succeeded — commit!
    $pdo->commit();
    echo "Order #$orderId placed successfully!";

} catch (Exception $e) {
    // Step 4: Something went wrong — roll back everything
    $pdo->rollBack();
    echo "Order failed: " . $e->getMessage();
    // The INSERT into orders and order_items are both undone
}

Transaction with Multiple Items

<?php
function placeOrder(PDO $pdo, int $userId, array $items): int {
    try {
        $pdo->beginTransaction();

        // Calculate total
        $total = array_sum(array_map(
            fn($item) => $item["price"] * $item["quantity"],
            $items
        ));

        // Create order
        $stmt = $pdo->prepare(
            "INSERT INTO orders (user_id, total, status) VALUES (?, ?, 'pending')"
        );
        $stmt->execute([$userId, $total]);
        $orderId = $pdo->lastInsertId();

        // Insert line items and deduct stock
        $insertItem = $pdo->prepare(
            "INSERT INTO order_items (order_id, product_id, quantity, price)
             VALUES (:oid, :pid, :qty, :price)"
        );

        $deductStock = $pdo->prepare(
            "UPDATE products SET stock = stock - :qty
             WHERE id = :pid AND stock >= :qty"
        );

        foreach ($items as $item) {
            // Add line item
            $insertItem->execute([
                "oid"   => $orderId,
                "pid"   => $item["product_id"],
                "qty"   => $item["quantity"],
                "price" => $item["price"],
            ]);

            // Deduct stock — fails if not enough
            $deductStock->execute([
                "qty" => $item["quantity"],
                "pid" => $item["product_id"],
            ]);

            if ($deductStock->rowCount() === 0) {
                throw new RuntimeException(
                    "Insufficient stock for product #{$item['product_id']}"
                );
            }
        }

        $pdo->commit();
        return $orderId;

    } catch (Exception $e) {
        $pdo->rollBack();
        throw $e; // Re-throw so the caller knows it failed
    }
}

// Usage
try {
    $orderId = placeOrder($pdo, userId: 1, items: [
        ["product_id" => 101, "quantity" => 2, "price" => 29.99],
        ["product_id" => 205, "quantity" => 1, "price" => 49.99],
    ]);
    echo "Order #$orderId placed!";
} catch (RuntimeException $e) {
    echo "Could not place order: " . $e->getMessage();
}

Money Transfer — Classic Transaction Example

<?php
function transferFunds(PDO $pdo, int $fromId, int $toId, float $amount): void {
    if ($amount <= 0) {
        throw new InvalidArgumentException("Amount must be positive");
    }

    try {
        $pdo->beginTransaction();

        // Deduct from sender
        $stmt = $pdo->prepare(
            "UPDATE accounts SET balance = balance - :amount
             WHERE id = :id AND balance >= :amount"
        );
        $stmt->execute(["amount" => $amount, "id" => $fromId]);

        if ($stmt->rowCount() === 0) {
            throw new RuntimeException("Insufficient funds");
        }

        // Credit to receiver
        $stmt = $pdo->prepare(
            "UPDATE accounts SET balance = balance + :amount WHERE id = :id"
        );
        $stmt->execute(["amount" => $amount, "id" => $toId]);

        if ($stmt->rowCount() === 0) {
            throw new RuntimeException("Recipient account not found");
        }

        // Log the transfer
        $stmt = $pdo->prepare(
            "INSERT INTO transfers (from_id, to_id, amount) VALUES (?, ?, ?)"
        );
        $stmt->execute([$fromId, $toId, $amount]);

        $pdo->commit();

    } catch (Exception $e) {
        $pdo->rollBack();
        throw $e;
    }
}

// Usage
try {
    transferFunds($pdo, fromId: 1, toId: 2, amount: 150.00);
    echo "Transfer complete!";
} catch (Exception $e) {
    echo "Transfer failed: " . $e->getMessage();
}

Checking Transaction State

<?php
// Check if we're currently inside a transaction
if ($pdo->inTransaction()) {
    echo "Transaction is active";
} else {
    echo "No active transaction";
}

// Useful for defensive programming in shared code:
function ensureTransaction(PDO $pdo): void {
    if (!$pdo->inTransaction()) {
        throw new LogicException("This method must be called within a transaction");
    }
}

Advanced Fetch Styles

In Lesson 19 you used FETCH_ASSOC (our default), FETCH_OBJ, and FETCH_COLUMN. Let's explore more fetch modes that can simplify your code significantly.

FETCH_CLASS — Map Rows to Objects

Instead of working with raw arrays, you can map database rows directly to class instances. The column values are assigned to properties with matching names.

<?php
class Product {
    public int $id;
    public string $name;
    public float $price;
    public string $category;
    public ?string $created_at;

    public function getFormattedPrice(): string {
        return "$" . number_format($this->price, 2);
    }

    public function isExpensive(): bool {
        return $this->price > 100;
    }
}

$stmt = $pdo->query("SELECT * FROM products ORDER BY name");
$products = $stmt->fetchAll(PDO::FETCH_CLASS, Product::class);

foreach ($products as $product) {
    echo "{$product->name}: {$product->getFormattedPrice()}";
    if ($product->isExpensive()) {
        echo " 💰";
    }
    echo "\n";
}
// "Mechanical Keyboard: $89.99"
// "Standing Desk: $299.99 💰"
// "USB-C Hub: $49.99"

FETCH_KEY_PAIR — Two Columns as Key => Value

Perfect for building lookup arrays, dropdown menus, and configuration maps from the database.

<?php
// Build a category dropdown: id => name
$stmt = $pdo->query("SELECT id, name FROM categories ORDER BY name");
$categories = $stmt->fetchAll(PDO::FETCH_KEY_PAIR);
// [1 => "Electronics", 2 => "Office", 3 => "Clothing"]

// Generate HTML <select>
echo '<select name="category_id">';
foreach ($categories as $id => $name) {
    echo "<option value=\"$id\">" . htmlspecialchars($name) . "</option>";
}
echo '</select>';

// Also great for config tables
$stmt = $pdo->query("SELECT setting_key, setting_value FROM settings");
$config = $stmt->fetchAll(PDO::FETCH_KEY_PAIR);
// ["site_name" => "My App", "max_uploads" => "10", "theme" => "dark"]

$siteName = $config["site_name"] ?? "Default";

FETCH_UNIQUE — Full Rows Keyed by First Column

<?php
// Key rows by user ID for quick lookup
$stmt = $pdo->query("SELECT id, name, email, role FROM users");
$usersById = $stmt->fetchAll(PDO::FETCH_UNIQUE);
// [
//   1 => ["name" => "Alice", "email" => "alice@...", "role" => "admin"],
//   2 => ["name" => "Bob",   "email" => "bob@...",   "role" => "user"],
// ]

// Direct access by ID — no need to loop!
if (isset($usersById[42])) {
    echo "User 42: {$usersById[42]['name']}";
}

FETCH_GROUP — Group Rows by First Column

<?php
// Group products by category
$stmt = $pdo->query(
    "SELECT category, id, name, price FROM products ORDER BY category, name"
);
$grouped = $stmt->fetchAll(PDO::FETCH_GROUP);
// [
//   "Electronics" => [
//       ["id" => 1, "name" => "Mouse", "price" => 29.99],
//       ["id" => 4, "name" => "Keyboard", "price" => 89.99],
//   ],
//   "Office" => [
//       ["id" => 3, "name" => "Desk Mat", "price" => 39.99],
//   ],
// ]

// Display grouped results
foreach ($grouped as $category => $products) {
    echo "<h3>" . htmlspecialchars($category) . " (" . count($products) . ")</h3>";
    foreach ($products as $product) {
        echo "  - {$product['name']}: \${$product['price']}\n";
    }
}

// Group users by role
$stmt = $pdo->query("SELECT role, name, email FROM users ORDER BY role, name");
$byRole = $stmt->fetchAll(PDO::FETCH_GROUP);
// ["admin" => [...], "editor" => [...], "user" => [...]]

FETCH_GROUP + FETCH_COLUMN — Grouped Single Values

<?php
// Get just the names grouped by category
$stmt = $pdo->query("SELECT category, name FROM products ORDER BY category, name");
$namesByCategory = $stmt->fetchAll(PDO::FETCH_GROUP | PDO::FETCH_COLUMN);
// [
//   "Electronics" => ["Keyboard", "Mouse", "USB-C Hub"],
//   "Office"      => ["Desk Mat", "Standing Desk"],
// ]

// Get email addresses grouped by role
$stmt = $pdo->query("SELECT role, email FROM users ORDER BY role");
$emailsByRole = $stmt->fetchAll(PDO::FETCH_GROUP | PDO::FETCH_COLUMN);
// ["admin" => ["alice@...", "diana@..."], "user" => ["bob@...", "charlie@..."]]

Fetch Style Quick Reference

CRUD Helper Methods

Let's extend the Database class from Lesson 19 with convenience methods for common CRUD operations. These reduce boilerplate and make your code more expressive.

<?php
// File: Database.php (extended from Lesson 19)

class Database {
    private static ?PDO $instance = null;

    private const DEFAULT_OPTIONS = [
        PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        PDO::ATTR_EMULATE_PREPARES   => false,
    ];

    private function __construct() {}
    private function __clone() {}

    public static function getConnection(): PDO {
        if (self::$instance === null) {
            $host   = $_ENV["DB_HOST"]   ?? "localhost";
            $dbname = $_ENV["DB_NAME"]   ?? "php_course";
            $user   = $_ENV["DB_USER"]   ?? "root";
            $pass   = $_ENV["DB_PASS"]   ?? "";
            $dsn = "mysql:host=$host;dbname=$dbname;charset=utf8mb4";

            try {
                self::$instance = new PDO($dsn, $user, $pass, self::DEFAULT_OPTIONS);
            } catch (PDOException $e) {
                error_log("DB connection failed: " . $e->getMessage());
                throw new RuntimeException("Database unavailable", 0, $e);
            }
        }
        return self::$instance;
    }

    /** Prepare and execute a query. */
    public static function run(string $sql, array $params = []): PDOStatement {
        $stmt = self::getConnection()->prepare($sql);
        $stmt->execute($params);
        return $stmt;
    }

    /** Fetch a single row or null. */
    public static function findOne(string $sql, array $params = []): ?array {
        $row = self::run($sql, $params)->fetch();
        return $row ?: null;
    }

    /** Fetch all rows. */
    public static function findAll(string $sql, array $params = []): array {
        return self::run($sql, $params)->fetchAll();
    }

    /** Fetch a single column value. */
    public static function value(string $sql, array $params = []): mixed {
        return self::run($sql, $params)->fetchColumn();
    }

    /** INSERT a row and return the new ID. */
    public static function insert(string $table, array $data): string {
        $columns = implode(", ", array_keys($data));
        $placeholders = implode(", ", array_map(fn($k) => ":$k", array_keys($data)));

        self::run("INSERT INTO $table ($columns) VALUES ($placeholders)", $data);
        return self::getConnection()->lastInsertId();
    }

    /** UPDATE rows and return the number of affected rows. */
    public static function update(string $table, array $data, string $where, array $whereParams = []): int {
        $set = implode(", ", array_map(fn($k) => "$k = :$k", array_keys($data)));

        return self::run(
            "UPDATE $table SET $set WHERE $where",
            array_merge($data, $whereParams)
        )->rowCount();
    }

    /** DELETE rows and return the number of affected rows. */
    public static function delete(string $table, string $where, array $params = []): int {
        return self::run("DELETE FROM $table WHERE $where", $params)->rowCount();
    }

    /** Execute a callback inside a transaction. */
    public static function transaction(callable $callback): mixed {
        $pdo = self::getConnection();
        $pdo->beginTransaction();
        try {
            $result = $callback($pdo);
            $pdo->commit();
            return $result;
        } catch (Exception $e) {
            $pdo->rollBack();
            throw $e;
        }
    }

    public static function close(): void {
        self::$instance = null;
    }
}

Using the Extended Database Class

<?php
require_once "Database.php";

// ---- CREATE ----
$userId = Database::insert("users", [
    "name"  => "Bruce Wayne",
    "email" => "bruce@wayne.com",
    "role"  => "admin",
]);
echo "New user ID: $userId\n";

// ---- READ ----
$user = Database::findOne("SELECT * FROM users WHERE id = ?", [$userId]);
echo "Found: {$user['name']}\n";

$admins = Database::findAll(
    "SELECT * FROM users WHERE role = ? ORDER BY name",
    ["admin"]
);
echo count($admins) . " admins found.\n";

$totalUsers = Database::value("SELECT COUNT(*) FROM users");
echo "Total: $totalUsers users\n";

// ---- UPDATE ----
$changed = Database::update(
    "users",
    ["name" => "Batman", "role" => "hero"],
    "id = :where_id",
    ["where_id" => $userId]
);
echo "$changed row(s) updated.\n";

// ---- DELETE ----
$deleted = Database::delete("users", "id = ?", [$userId]);
echo "$deleted row(s) deleted.\n";

// ---- TRANSACTION ----
$orderId = Database::transaction(function (PDO $pdo) {
    $stmt = $pdo->prepare("INSERT INTO orders (user_id, total) VALUES (?, ?)");
    $stmt->execute([1, 99.99]);
    $orderId = $pdo->lastInsertId();

    $stmt = $pdo->prepare(
        "INSERT INTO order_items (order_id, product_id, quantity) VALUES (?, ?, ?)"
    );
    $stmt->execute([$orderId, 101, 2]);

    return $orderId;
});
echo "Order #$orderId placed in a transaction.\n";

Hands-On Exercises

<?php
$dsn = "mysql:host=localhost;dbname=php_course;charset=utf8mb4";
$pdo = new PDO($dsn, "root", "", [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,
]);

// 1. Create table
$pdo->exec("CREATE TABLE IF NOT EXISTS tasks (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    description TEXT,
    status ENUM('pending','in_progress','done') DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)");
echo "Table ready.\n\n";

// 2. Insert 5 tasks
$stmt = $pdo->prepare(
    "INSERT INTO tasks (title, description) VALUES (:title, :desc)"
);

$tasks = [
    ["title" => "Set up project",      "desc" => "Initialize repo and install deps"],
    ["title" => "Design database",     "desc" => "Create ER diagram and schema"],
    ["title" => "Build user auth",     "desc" => "Login, register, logout"],
    ["title" => "Create dashboard",    "desc" => "Main app interface"],
    ["title" => "Write documentation", "desc" => "README and API docs"],
];

$ids = [];
foreach ($tasks as $task) {
    $stmt->execute($task);
    $ids[] = $pdo->lastInsertId();
}
echo "Inserted " . count($ids) . " tasks. IDs: " . implode(", ", $ids) . "\n\n";

// 3. Update first task to 'in_progress'
$stmt = $pdo->prepare("UPDATE tasks SET status = ? WHERE id = ?");
$stmt->execute(["in_progress", $ids[0]]);
echo "Updated task #{$ids[0]}: {$stmt->rowCount()} row(s) changed.\n";

// 4. Update last task to 'done'
$stmt->execute(["done", end($ids)]);
echo "Updated task #" . end($ids) . ": {$stmt->rowCount()} row(s) changed.\n\n";

// 5. Delete tasks with status 'done'
$stmt = $pdo->prepare("DELETE FROM tasks WHERE status = ?");
$stmt->execute(["done"]);
echo "Deleted {$stmt->rowCount()} completed task(s).\n\n";

// 6. Fetch remaining tasks
$remaining = $pdo->query("SELECT id, title, status FROM tasks ORDER BY id")->fetchAll();
echo "Remaining tasks:\n";
foreach ($remaining as $task) {
    echo "  #{$task['id']} [{$task['status']}] {$task['title']}\n";
}

<?php
$dsn = "mysql:host=localhost;dbname=php_course;charset=utf8mb4";
$pdo = new PDO($dsn, "root", "", [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,
]);

// Setup
$pdo->exec("DROP TABLE IF EXISTS warehouse_stock");
$pdo->exec("CREATE TABLE warehouse_stock (
    id INT AUTO_INCREMENT PRIMARY KEY,
    warehouse VARCHAR(50) NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL DEFAULT 0,
    UNIQUE KEY (warehouse, product_id)
)");

$stmt = $pdo->prepare(
    "INSERT INTO warehouse_stock (warehouse, product_id, quantity) VALUES (?, ?, ?)"
);
$stmt->execute(["Warehouse A", 1, 100]);
$stmt->execute(["Warehouse B", 1, 20]);
echo "Setup complete: A=100, B=20\n\n";

// Transfer function
function transferStock(PDO $pdo, string $from, string $to, int $productId, int $qty): void {
    if ($qty <= 0) {
        throw new InvalidArgumentException("Quantity must be positive");
    }

    try {
        $pdo->beginTransaction();

        // Deduct from source
        $stmt = $pdo->prepare(
            "UPDATE warehouse_stock SET quantity = quantity - :qty
             WHERE warehouse = :wh AND product_id = :pid AND quantity >= :qty"
        );
        $stmt->execute(["qty" => $qty, "wh" => $from, "pid" => $productId]);

        if ($stmt->rowCount() === 0) {
            throw new RuntimeException(
                "Insufficient stock in $from (need $qty)"
            );
        }

        // Add to destination
        $stmt = $pdo->prepare(
            "UPDATE warehouse_stock SET quantity = quantity + :qty
             WHERE warehouse = :wh AND product_id = :pid"
        );
        $stmt->execute(["qty" => $qty, "wh" => $to, "pid" => $productId]);

        if ($stmt->rowCount() === 0) {
            throw new RuntimeException("Destination $to not found");
        }

        $pdo->commit();
        echo "Transferred $qty units from $from to $to.\n";

    } catch (Exception $e) {
        $pdo->rollBack();
        throw $e;
    }
}

// Test valid transfer
try {
    transferStock($pdo, "Warehouse A", "Warehouse B", 1, 30);
} catch (Exception $e) {
    echo "FAILED: " . $e->getMessage() . "\n";
}

// Check stock
$stock = $pdo->query(
    "SELECT warehouse, quantity FROM warehouse_stock WHERE product_id = 1 ORDER BY warehouse"
)->fetchAll();
foreach ($stock as $row) {
    echo "  {$row['warehouse']}: {$row['quantity']} units\n";
}
echo "\n";

// Test invalid transfer (should fail)
try {
    transferStock($pdo, "Warehouse A", "Warehouse B", 1, 200);
} catch (Exception $e) {
    echo "FAILED (expected): " . $e->getMessage() . "\n";
}

// Verify stock unchanged after failed transfer
$stock = $pdo->query(
    "SELECT warehouse, quantity FROM warehouse_stock WHERE product_id = 1 ORDER BY warehouse"
)->fetchAll();
foreach ($stock as $row) {
    echo "  {$row['warehouse']}: {$row['quantity']} units\n";
}

Summary

📚 Additional Resources

• PHP Manual: PDO Transactions
• PHP Manual: PDO::lastInsertId
• PHP Manual: PDOStatement::rowCount
• PHP Manual: PDOStatement::fetchAll (all fetch modes)
• PHP Delusions: PDO Tutorial

🚀 What's Next?

You now have every PDO skill you need. In Lesson 21: Building a CRUD App, you'll put it all together — building a complete task manager application with a form for creating tasks, a list view, inline editing, deletion with confirmation, and search. It's the culmination of everything from Modules 4, 5, and 6.