In what situations should transactions be used in PHP scripts to ensure data integrity when interacting with a MySQL database?

Transactions should be used in PHP scripts when multiple database queries need to be executed together as a single unit of work to ensure data integrity. This is important when there are dependencies between the queries and you want to maintain consistency in the database. By using transactions, you can ensure that either all the queries are successfully executed or none of them are, preventing partial updates or inconsistencies in the data. 

// Establish a connection to the MySQL database
$connection = new mysqli($servername, $username, $password, $dbname);

// Start a transaction
$connection->begin_transaction();

// Execute multiple queries within the transaction
$query1 = "INSERT INTO table1 (column1) VALUES ('value1')";
$query2 = "UPDATE table2 SET column2 = 'new_value' WHERE condition = 'value'";
$connection->query($query1);
$connection->query($query2);

// Commit the transaction if all queries are successful
$connection->commit();

// Rollback the transaction if any query fails
$connection->rollback();

// Close the database connection
$connection->close();

Related Questions