PHP Transactions

PHP Transactions – Ensure Data Integrity in Databases 🔄

In PHP, a transaction is a set of database operations that are executed together. If any of them fail, the whole transaction is rolled back (undoing all changes). This ensures **data consistency** and prevents incomplete operations.

Imagine you’re transferring money from Alice to Bob 💰. If the money is deducted from Alice’s account but fails to be added to Bob’s, that would be a disaster! 🚨 Transactions prevent such issues.

🔹 Why Use Transactions?

Transactions are useful when:

  • You need to perform multiple database operations at once.
  • All operations must either succeed together or fail together.
  • You want to prevent inconsistent database states.

1️⃣ Transactions in MySQLi

📝 Example: Money Transfer Using MySQLi Transactions

In this example, we ensure that both **debiting Alice** and **crediting Bob** succeed, or neither happens.

<?php
$servername = "localhost";
$username = "root";
$password = "";
$database = "bank_db";

// Create connection
$conn = new mysqli($servername, $username, $password, $database);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// Start transaction
$conn->begin_transaction();

try {
    // Deduct from Alice
    $conn->query("UPDATE accounts SET balance = balance - 500 WHERE name = 'Alice'");
    
    // Credit to Bob
    $conn->query("UPDATE accounts SET balance = balance + 500 WHERE name = 'Bob'");
    
    // Commit the transaction
    $conn->commit();
    echo "Transaction successful! ✅";
} catch (Exception $e) {
    // Rollback if any query fails
    $conn->rollback();
    echo "Transaction failed! ❌";
}

// Close connection
$conn->close();
?>

Try It Now

2️⃣ Transactions in PDO

📝 Example: Money Transfer Using PDO Transactions

Same concept but using PDO.

<?php
$dsn = "mysql:host=localhost;dbname=bank_db";
$username = "root";
$password = "";

try {
    // Create PDO instance
    $pdo = new PDO($dsn, $username, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // Start transaction
    $pdo->beginTransaction();

    // Deduct from Alice
    $pdo->exec("UPDATE accounts SET balance = balance - 500 WHERE name = 'Alice'");
    
    // Credit to Bob
    $pdo->exec("UPDATE accounts SET balance = balance + 500 WHERE name = 'Bob'");

    // Commit transaction
    $pdo->commit();
    echo "Transaction successful! ✅";
} catch (Exception $e) {
    // Rollback if any query fails
    $pdo->rollback();
    echo "Transaction failed! ❌";
}

// Close connection
$pdo = null;
?>

Try It Now

🎯 Key Takeaways

  • begin_transaction() (MySQLi) or beginTransaction() (PDO) starts a transaction.
  • commit() saves changes to the database.
  • rollback() undoes changes if something goes wrong.

🚀 Next Steps

Try modifying the examples above and test transactions by introducing errors. See how rollback prevents partial updates! 🛠️