PHP PDO MySQL Tutorial: Prepared Statements, Transactions & Error Handling

2. Recommended PDO Connection Configuration

<?php
$dsn = 'mysql:host=127.0.0.1;dbname=aureviafin_db;charset=utf8mb4';
$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false, // Enforce native engine prepares
];

try {
    $pdo = new PDO($dsn, 'db_user', 'secure_password', $options);
} catch (PDOException $e) {
    error_log('Database connection error: ' . $e->getMessage());
    die('Database connection failure. Please contact administrator.');
}

4. Managing Multi-Table ACID Transactions

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

    // Deduct source account balance
    $stmt1 = $pdo->prepare('UPDATE accounts SET balance = balance - :amount WHERE id = :source_id');
    $stmt1->execute([':amount' => 500.00, ':source_id' => 14]);

    // Credit destination account balance
    $stmt2 = $pdo->prepare('UPDATE accounts SET balance = balance + :amount WHERE id = :dest_id');
    $stmt2->execute([':amount' => 500.00, ':dest_id' => 29]);

    // Commit both operations simultaneously
    $pdo->commit();
} catch (Throwable $e) {
    // If any step fails, roll back completely
    if ($pdo->inTransaction()) {
        $pdo->rollBack();
    }
    throw $e;
}