PHP MySQLi mysqli_commit() - Committing a Transaction

mysqli_commit()

The mysqli_commit() function commits the current transaction. This means that all commands executed after turning off autocommit are permanently written to the database.

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());
}

// Disable autocommit
mysqli_autocommit($connection, FALSE);

// Insert two new records (temporarily waiting in memory)
mysqli_query($connection, "INSERT INTO mysqli_table (fruit, vegetable) VALUES ('Kiwi', 'Spinach')");
mysqli_query($connection, "INSERT INTO mysqli_table (fruit, vegetable) VALUES ('Melon', 'Bean')");

// Execute and permanently lock the transaction into the database
// NOTE: If we did not call the mysqli_commit() function below, both of the above 
// INSERT commands would simply be discarded upon closing the connection, and there 
// would be no changes in the database. Only this command actually writes them!
if (!mysqli_commit($connection)) {
    echo "Transaction commit failed.";
} else {
    echo "Transaction commit successful.";
}

mysqli_close($connection);
?>

RESULT

The result will be displayed here...

Practical use: We use transactions when we want to ensure that either all commands are executed or none at all (e.g., during a money transfer). Test it at home like this:

Turn off autocommit in the code and execute insertions.

Without calling mysqli_commit(), you won't see the data in the database.

Once you add this function, the changes become visible to all users.