What are the best practices for updating multiple tables in a single SQL query using PHP?

When updating multiple tables in a single SQL query using PHP, it is important to use transactions to ensure data integrity. This involves starting a transaction, executing the SQL query to update the tables, and then committing the transaction if all updates are successful. If an error occurs during the updates, the transaction can be rolled back to maintain consistency across the tables. 

<?php

// Establish a connection to the database
$pdo = new PDO("mysql:host=localhost;dbname=mydatabase", "username", "password");

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

try {
    // Update multiple tables in a single SQL query
    $sql = "UPDATE table1 SET column1 = 'value' WHERE id = 1; 
            UPDATE table2 SET column2 = 'value' WHERE id = 1;";
    
    $stmt = $pdo->exec($sql);

    // Commit the transaction if all updates are successful
    $pdo->commit();
    
    echo "Tables updated successfully!";
} catch (Exception $e) {
    // Roll back the transaction if an error occurs
    $pdo->rollBack();
    
    echo "Error updating tables: " . $e->getMessage();
}

?>

Keywords

SQL PHP multiple tables single query update

Related Questions