PHP PDO Advanced

<?php
try {
    $pdo->beginTransaction();

    $pdo->prepare("UPDATE accounts SET balance = balance - ? WHERE id = ?")
        ->execute([100, $fromId]);

    $pdo->prepare("UPDATE accounts SET balance = balance + ? WHERE id = ?")
        ->execute([100, $toId]);

    $pdo->prepare("INSERT INTO transfers (from_id, to_id, amount) VALUES (?, ?, ?)")
        ->execute([$fromId, $toId, 100]);

    $pdo->commit();
} catch (Throwable $e) {
    if ($pdo->inTransaction()) {
        $pdo->rollBack();
    }
    throw $e;
}

<?php
$pdo->beginTransaction();

try {
    saveOrder($pdo, $order);

    $pdo->exec("SAVEPOINT before_refund");
    try {
        applyRefund($pdo, $order);
    } catch (RefundException $e) {
        // Roll back just the refund - keep the order saved
        $pdo->exec("ROLLBACK TO SAVEPOINT before_refund");
        notifyOps($e);
    }

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

<?php
$stmt = $pdo->prepare(
    "INSERT INTO log_entries (level, message, created_at)
     VALUES (?, ?, NOW())"
);

$pdo->beginTransaction();
try {
    foreach ($events as $event) {
        $stmt->execute([$event->level, $event->message]);
    }
    $pdo->commit();
} catch (Throwable $e) {
    $pdo->rollBack();
    throw $e;
}
// 10-100x faster than auto-committing each insert

<?php
function bulkInsert(PDO $pdo, string $table, array $rows): int {
    if (!$rows) return 0;

    $columns = array_keys($rows[0]);
    $colList = "(" . implode(",", $columns) . ")";

    // Build "(?,?,?), (?,?,?), ..." with one tuple per row
    $tuple   = "(" . implode(",", array_fill(0, count($columns), "?")) . ")";
    $tuples  = implode(",", array_fill(0, count($rows), $tuple));

    $sql = "INSERT INTO $table $colList VALUES $tuples";

    // Flatten all values in row-major order
    $values = [];
    foreach ($rows as $row) {
        foreach ($columns as $c) $values[] = $row[$c];
    }

    $stmt = $pdo->prepare($sql);
    $stmt->execute($values);
    return $stmt->rowCount();
}

bulkInsert($pdo, "users", [
    ["name" => "Alice", "email" => "a@x.com"],
    ["name" => "Bob",   "email" => "b@x.com"],
]);

DELIMITER //
CREATE PROCEDURE add_credit(IN user_id INT, IN amount DECIMAL(10,2))
BEGIN
    UPDATE accounts SET balance = balance + amount WHERE id = user_id;
    INSERT INTO credits (user_id, amount, created_at)
    VALUES (user_id, amount, NOW());
END //
DELIMITER ;

<?php
$stmt = $pdo->prepare("CALL add_credit(?, ?)");
$stmt->execute([$userId, 50.00]);

// OUT parameter procedures need session vars on MySQL
$pdo->exec("CALL get_balance($userId, @bal)");
$row = $pdo->query("SELECT @bal AS balance")->fetch();
echo $row["balance"];

<?php
class User {
    public int $id;
    public string $name;
    public string $email;
    public ?DateTimeImmutable $createdAt = null;
}

$stmt = $pdo->prepare("SELECT id, name, email, created_at FROM users WHERE id = ?");
$stmt->execute([$id]);
$stmt->setFetchMode(PDO::FETCH_CLASS, User::class);
$user = $stmt->fetch();

// All rows
$stmt = $pdo->query("SELECT * FROM users");
$users = $stmt->fetchAll(PDO::FETCH_CLASS, User::class);

// Constructor mode - pass row values into __construct
$stmt = $pdo->query("SELECT id, name FROM users");
$users = $stmt->fetchAll(PDO::FETCH_CLASS | PDO::FETCH_PROPS_LATE, User::class);

<?php
function rowGenerator(PDOStatement $stmt): Generator {
    while ($row = $stmt->fetch()) {
        yield $row;
    }
}

// Process millions of rows without loading them all
$stmt = $pdo->query("SELECT * FROM huge_table");
foreach (rowGenerator($stmt) as $row) {
    process($row);
}

<?php
$pdo->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false);
$stmt = $pdo->query("SELECT * FROM huge_table");
foreach (rowGenerator($stmt) as $row) { /* ... */ }