How can PHP developers ensure data consistency when updating multiple tables in a database?

To ensure data consistency when updating multiple tables in a database, PHP developers can use database transactions. By wrapping the multiple SQL queries in a transaction block, developers can ensure that either all the queries are executed successfully or none of them are, maintaining data integrity. 

<?php
// Connect to the database
$pdo = new PDO('mysql:host=localhost;dbname=mydatabase', 'username', 'password');

// Begin a transaction
$pdo->beginTransaction();

try {
    // Update table 1
    $pdo->exec("UPDATE table1 SET column1 = 'new_value' WHERE id = 1");

    // Update table 2
    $pdo->exec("UPDATE table2 SET column2 = 'new_value' WHERE id = 1");

    // Commit the transaction
    $pdo->commit();
} catch (Exception $e) {
    // Rollback the transaction if an error occurs
    $pdo->rollback();
    echo "Transaction failed: " . $e->getMessage();
}
?>

Keywords

transactions atomicity ACID database normalization foreign keys

Related Questions