PHP MySQLi autocommit() - Transaction Management

mysqli_autocommit() and mysqli_commit()

By default, MySQL saves every command immediately. We can stop this with the

mysqli_autocommit($connection, 
    FALSE)

function. This allows us to execute multiple commands and only confirm them all at once at the very end with mysqli_commit(). If something goes wrong, we can roll everything back.

EXAMPLE

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

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

if (!$connection) {
    die("Connection failed: " . mysqli_connect_error());
}

// 1. Turn off autocommit (preparing for a transaction)
mysqli_autocommit($connection, FALSE);

// 2. Perform data insertion
// NOTE: We currently have 4 rows in the database (IDs 1 through 4).
// Since the table_id has the AUTO_INCREMENT property, we do not need to 
// manually set the row number. The database will determine that the last 
// ID is 4, automatically add +1, and save this new record in the next 
// available slot, which is table_id = 5.
mysqli_query($connection, "INSERT INTO mysqli_table (fruit, vegetable) VALUES ('Grape', 'Bell pepper')");

// 3. Now, manually commit all changes
if (mysqli_commit($connection)) {
    echo "New record was added successfully.";
} else {
    echo "An error occurred while saving.";
}

mysqli_close($connection);
?>

RESULT

The result will be displayed here...

Local environment tip: When testing transactions on your computer, follow these steps to see the changes live:

Start the Apache and MySQL modules in the XAMPP Control Panel.

After running the code, open phpMyAdmin in your browser.

Select your table and check if the data was actually saved after the commit() command.