PHP MySQL Transactions with Examples

In this tutorial, we are going to see transactions in PHP MySQL with examples. Transaction is a logical unit of work that contains one or more SQL statements. Transactions are atomic units of work that can be committed or rolled back. When a transaction makes multiple changes to the database, all changes succeed when the transaction is committed or all changes are canceled when the transaction is rolled back.
 

 
 

Transactions in PHP MySQL with examples

If you are doing a money transfer transaction from one bank account to another, at that time, if there is an interruption due to the Internet/Server or some other problem, then the transaction will be rolled back to its original stage and your money will be refunded to your account.

In this tutorial, we will use the “Customers” and “Orders” tables mentioned below.

mysql> SELECT * FROM Customers;
+------------+------------------+----------+
|    P_ID    |       NAME       |    AGE   |
+------------+------------------+----------+
|          1 | Alex Babtise     |       22 |
|          2 | Eric Maxich      |       30 |
|          3 | Yohan Suinol     |       18 |
+------------+------------------+----------+
3 rows in set (0.00 sec)

 

mysql> SELECT * FROM Orders;
+------------+--------------+----------+
|    C_ID    |   NUM_ORD    |   P_ID   |
+------------+--------------+----------+
|          1 | 001259       |       2  |
|          2 | 005896       |       1  |
|          3 | 009671       |       3  |
+------------+--------------+----------+
3 rows in set (0.00 sec)

Consider that you should add “Thomas Suzik” as a new customer with his order. You would execute the following two INSERT statements.

INSERT INTO `Customers` (`p_id`, `name`, `age`) 
VALUES (4, 'Thomas Suzik', 50);

INSERT INTO `Orders` (`c_id`, `num_ord`, `p_id`) 
VALUES (8, '009741', 4);

 
You can see that in the second statement 4 is given for `P_ID`, which is the value of `P_ID` in the first query. When you run these two statements, assume that the first fails and the second succeeds.

Then the `Orders` table will have a row that refers to a Customer whose id is 4 and who does not exist. If we have executed these two statements in a MySQL transaction, if the first statement fails, the second statement will be rolled back, without modification.
 
In PHP we can execute a transaction using MySQLi:

<?php

$p_id = 4;

//connect to mysql database
$conn = mysqli_connect('localhost', 'root', ' ', 'test_db');

mysqli_autocommit($conn, false);

$state = true;

$req1 = "INSERT INTO `Customers` (`p_id`, `name`, `age`) VALUES ($p_id, 'Thomas Suzik', 50)";
$req2 = "INSERT INTO `Orders` (`c_id`, `num_ord`, `p_id`) VALUES (8, '009741', $p_id)";

$res = mysqli_query($conn, $req1);

if (!$res) {
    $state = false;
    echo "Error: " . mysqli_error($conn) . ".";
}

$res = mysqli_query($conn, $req2);

if (!$res) {
    $state = false;
    echo "Error: " . mysqli_error($conn) . ".";
}

if ($state) {
    mysqli_commit($conn);
    echo "All queries have been executed successfully";
} else {
    mysqli_rollback($conn);
    echo "All queries have been canceled";
} 

mysqli_close($conn);

?>

When you run the function mysqli_query(), the result is immediately validated in the database. By using the mysqli_autocommit() function, you can disable this behavior so that the result is not permanently validated in the database until you validate it.

At the end, if the variable “state” contains true (So, no errors occurred), we validate the results in the database permanently using mysqli_commit(). Otherwise, we roll back the results using mysqli_rollback().
 
MCQPractice competitive and technical Multiple Choice Questions and Answers (MCQs) with simple and logical explanations to prepare for tests and interviews.Read More