What are the best practices for updating data in multiple tables from different databases in PHP?

When updating data in multiple tables from different databases in PHP, it is best to use transactions to ensure data consistency. Begin by establishing connections to the databases using PDO or MySQLi. Start a transaction, perform the updates on each database within the transaction, and commit the changes if all updates are successful. If any update fails, rollback the transaction to maintain data integrity. 

try {
    // Connect to the first database
    $pdo1 = new PDO("mysql:host=hostname1;dbname=database1", "username1", "password1");
    
    // Connect to the second database
    $pdo2 = new PDO("mysql:host=hostname2;dbname=database2", "username2", "password2");
    
    // Begin a transaction
    $pdo1->beginTransaction();
    $pdo2->beginTransaction();
    
    // Update data in the first database
    $pdo1->exec("UPDATE table1 SET column1 = 'new_value' WHERE condition1");
    
    // Update data in the second database
    $pdo2->exec("UPDATE table2 SET column2 = 'new_value' WHERE condition2");
    
    // Commit the transaction if all updates are successful
    $pdo1->commit();
    $pdo2->commit();
    
    echo "Data updated successfully in both databases.";
} catch (PDOException $e) {
    // Rollback the transaction if an error occurs
    $pdo1->rollBack();
    $pdo2->rollBack();
    
    echo "Error updating data: " . $e->getMessage();
}

Keywords

PHP SQL database transactions foreign keys

Related Questions