Advanced PDO 🗄️

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

ASSOC:  Alice is 28
OBJ:    Bob is 35
CLASS:  Hi, I'm Alice (28)
CLASS:  Hi, I'm Bob (35)

<?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",   "alice@example.com",   28],
    ["Bob",     "bob@example.com",     35],
    ["Charlie", "charlie@example.com", 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
}

Last inserted id: 3
Rows updated: 2

Everyone:
  Alice (29)
  Bob (35)
  Charlie (23)

<?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";
}

Aged 30+:
  Bob (35)
  Cara (41)
age >= 28: 3
age >= 35: 2
age >= 41: 1

<?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"]);
}

OK:   $200 Alice -> Bob
FAIL: Insufficient funds for Bob (rolled back)

Final balances:
  Alice  $800
  Bob    $700

<?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";
}

Matched:
  Alice
  Cara
  Dan

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

Rex is a dog

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

Transfer committed
  Alice: 60
  Bob: 40