PHP MySQLi begin_transaction() - Beginning a Transaction

mysqli_begin_transaction()

The mysqli_begin_transaction() function starts a new transaction. This means that all subsequent commands (e.g., fruit insertion) are not permanently saved to the database until we confirm them with the mysqli_commit() function.

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. Start the transaction
mysqli_begin_transaction($connection);

// 2. Insert two new records simultaneously
// NOTE: Since we started a transaction, both INSERT commands below 
// will wait in a safe mode. We have 4 rows in the database, so these two 
// records will be prepared for table_id = 5 and table_id = 6 
// due to the AUTO_INCREMENT rule.
mysqli_query($connection, "INSERT INTO mysqli_table (fruit, vegetable) VALUES ('Grape', 'Bell pepper')");
mysqli_query($connection, "INSERT INTO mysqli_table (fruit, vegetable) VALUES ('Peach', 'Tomato')");

// 3. Confirm and permanently save both records simultaneously
if (mysqli_commit($connection)) {
    echo "New records were added successfully.";
}

mysqli_close($connection);
?>

RESULT

The result will be displayed here...

Check in your environment: In the simulator below, you will see the final list of fruit in the table. If you are testing the code at home, follow these steps:

Copy the code into a PHP file and run it via XAMPP.

Open phpMyAdmin and select the orchard table.

Click "Browse", where Apple and Banana should be displayed.