In what situations should transactions be used in SQL queries to ensure data integrity when updating database records in PHP?

Transactions should be used in SQL queries when updating database records in PHP to ensure data integrity in situations where multiple queries need to be executed as a single unit of work. This helps to maintain consistency in the database by either committing all changes or rolling them back in case of an error. Using transactions also helps to prevent issues like partial updates or data corruption. 

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

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

try {
    // Execute multiple SQL queries within the transaction
    $pdo->exec("UPDATE table1 SET column1 = 'value1' WHERE id = 1");
    $pdo->exec("UPDATE table2 SET column2 = 'value2' WHERE id = 2");

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

Keywords

transactions SQL queries data integrity database records PHP

Related Questions