How can you ensure data consistency and integrity when updating multiple tables with related data in PHP and MySQL?

When updating multiple tables with related data in PHP and MySQL, you can ensure data consistency and integrity by using transactions. Transactions allow you to perform a series of SQL queries as a single unit of work, ensuring that either all queries are executed successfully or none of them are. This helps maintain data integrity by preventing partial updates that could leave your database in an inconsistent state. 

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

try {
    // Update table 1
    $stmt1 = $pdo->prepare("UPDATE table1 SET column1 = :value1 WHERE id = :id");
    $stmt1->execute(['value1' => $value1, 'id' => $id]);

    // Update table 2
    $stmt2 = $pdo->prepare("UPDATE table2 SET column2 = :value2 WHERE id = :id");
    $stmt2->execute(['value2' => $value2, 'id' => $id]);

    // Commit the transaction if all queries were successful
    $pdo->commit();
} catch (Exception $e) {
    // Rollback the transaction if an error occurred
    $pdo->rollBack();
    echo "Transaction failed: " . $e->getMessage();
}

Keywords

PHP MySQL transactions foreign keys data integrity

Related Questions