PHP MySQLi mysqli_rollback() - Transaction Rollback

mysqli_rollback()

The mysqli_rollback() function rolls back (undoes) all changes of the current transaction for the specified connection. This means that the database is reverted to the state it was in before the transaction started.

EXAMPLE

<?php
$server = "localhost";
$username = "root";
$password = "";
$database = "mysqli_database";

$connection = mysqli_connect($server, $username, $password, $database);

if (mysqli_connect_errno()) {
    echo "Connection failed: " . mysqli_connect_error();
    exit();
}

// 1. Disable autocommit (necessary to start a transaction)
mysqli_autocommit($connection, FALSE);

// 2. Perform some trial inserts (these are only stored temporarily in the database memory)
mysqli_query($connection, "INSERT INTO mysqli_table (fruit, vegetable) VALUES ('Greengage', 'Arugula')");
mysqli_query($connection, "INSERT INTO mysqli_table (fruit, vegetable) VALUES ('Grapes', 'Radish')");

// 3. Simulate an error or a condition for cancellation
if (true) { 
    // NOTE: Because the condition is true, mysqli_rollback() will execute. This function instructs the 
    // database to simply discard the previous two INSERT. The database table will remain completely 
    // untouched (it will still have only 4 rows).
    mysqli_rollback($connection);
    echo "Transaction was rolled back. No new records in the database.";
} else {
    mysqli_commit($connection);
    echo "Changes were permanently saved.";
}

mysqli_close($connection);
?>

RESULT

The result will be displayed here...

Practical use: We use transactions when executing multiple related operations (e.g., transferring money between accounts). If any step fails, we call mysqli_rollback() to prevent partial or incorrect entries in the database.

For transactions to work, tables must support the InnoDB storage engine.

Before use, we must disable automatic committing using mysqli_autocommit($connection, FALSE).

An excellent tool for ensuring data integrity (ACID standard).