WIKI webhosting

Transactions are a crucial concept in database systems, enabling atomicity, consistency, isolation, and durability (ACID properties) of operations. In the MySQL environment and its mysqli interface in PHP, transactions can be utilized to ensure data integrity when executing multiple interdependent database operations. In this article, we'll discuss how to work with transactions in mysqli, from initiation to either committing or rolling back.

Fundamentals of Transactions

A transaction is a sequence of database operations considered as a single logical unit of work. If any operation within a transaction fails, the entire transaction can be rolled back, meaning none of the operations within the transaction will have a lasting effect on the database.

Initialization and Configuration of mysqli

To use mysqli transactions, you first need to create an instance of mysqli and establish a connection to the database. It's important for the database to support transactions, typically achieved by using an engine such as InnoDB for MySQL.

$mysqli = new mysqli("host", "user", "password", "database");
if ($mysqli->connect_error) {
    die("Connection to database failed: " . $mysqli->connect_error);
}

Starting a Transaction

We initiate a transaction by turning off autocommit, meaning MySQL won't execute any changes to the database until we explicitly request a commit.

$mysqli->autocommit(FALSE);

Working with a Transaction

Now, we can perform database operations such as inserts, updates, or deletes. These operations will be part of our transaction.

try {
    $mysqli->query("INSERT INTO table (column) VALUES ('value')");
    $mysqli->query("UPDATE table SET column = 'new_value' WHERE condition = 'met'");
    // Additional operations...
    
    // Committing the transaction
    $mysqli->commit();
} catch (Exception $e) {
    // In case of an error, rollback the changes
    $mysqli->rollback();
    echo "Transaction error: " . $e->getMessage();
}

Ending the Transaction

We end the transaction either by committing it if everything proceeded smoothly or by rolling back the changes if an error occurred. It's a good practice to re-enable autocommit after completing the transaction.

$mysqli->autocommit(TRUE);

Utilizing transactions in mysqli is essential to ensure data integrity and consistency when executing complex operations involving multiple steps. With transactions, we can guarantee that either all operations will be successfully completed, or no change will be made to the database. It's a vital tool for any developer working with databases.