While learning and using MySQL queries in your applications, it is very important that you follow the ACID properties in order to develop an ideal database system. If you want to know more about ACID properties, click here to read the full tutorial with real-time examples.
In order to ensure the data integrity of the database, you must handle MySQL transactions effectively. In this tutorial, we will see what is MySQL transaction, why we need to focus on it and how to achieve data integrity using available PHP methods.
Also read: PHP MySQL- Retrieving Data From Table
What is Transaction?
A transaction is a collection of interconnected SQL statements that must complete in an all-or-nothing fashion. If all SQL statements run correctly, the transaction is considered successful. To avoid data inconsistency, if any statement fails, the system will revert to the previous state.
A real-time example of a transaction is banking transactions.
Suppose user A sends money to user B. Here, money should be deducted from A’s account and credited to B’s account. Pretty simple, right!
But what if money gets deducted from A’s account but doesn’t get credited into B’s account? That’s a serious problem, right? It breaks the database consistency.
In the above case, if one banking transaction fails, other transactions depending on it must also fail.
Overall, a database transaction makes sure either all statements should execute successfully or none of them should.
MySQL Transaction in PHP
In PHP, whenever you write a SQL query, it makes changes in the database without the need of writing a commit method. Because whenever you establish a connection in a PHP program, the auto_commit mode is set to true by default.
Whenever the auto_commit mode is set to true, each SQL statement acts as a single transaction.
In order to perform a transaction in PHP-MySQL, we need to turn off the auto_commit mode first.
The second step is to commit the changes using the commit() method only and only if all SQL statements execute successfully.
If any one of the SQL statements fails, we will roll back using the rollback() method.
PHP-MySQL Transaction Example
Here, we will first create a table to hold the user id, user name and balance. We will demonstrate the transaction example by performing a money transfer between two accounts.
First, create a table and insert data into it.
CREATE TABLE payments(
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100),
balance FLOAT
);
INSERT INTO payments(name,balance)
VALUES("Jay Vyas",1000),("Ishwar Rane", 300);Code language: SQL (Structured Query Language) (sql)Let’s check if the table is created successfully and if data is inserted into it correctly.
SELECT * FROM payments;Code language: SQL (Structured Query Language) (sql)
As you can see, the data is inserted into the table correctly.
Let’s now proceed to write a PHP program to demonstrate the transaction.
First, establish a connection and create a connection object.
<?php
$server = "localhost";
$user = "root";
$password = "";
$database = "journaldev";
$conn = mysqli_connect($server, $user, $password, $database);
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
?>Code language: PHP (php)Now, the important step is to turn off the auto_commit mode.
mysqli_autocommit($conn, FALSE);Code language: PHP (php)Now we will create a function to transfer the money from one account to another account.
To achieve this, we will use the UPDATE statement to update the balance of both the sender as well as the receiver.
But first, we will write a function to check if the sender and receiver accounts exist or not.
function ifExists($id)
{
global $conn;
$sql = "SELECT * FROM payments WHERE id = $id";
$result = mysqli_query($conn, $sql);
if (mysqli_num_rows($result) > 0) {
return true;
} else {
return false;
}
}Code language: PHP (php)Now, let’s write a code for the transfer function.
function transfer($from, $to, $amount)
{
global $conn;
if (!ifExists($from)) {
echo "<br>Account $from does not exist";
return false;
}
// check if the balance is sufficient for the transfer
$bQuery = "select balance from payments where id=$from";
$bResult = mysqli_query($conn, $bQuery);
$bRow = mysqli_fetch_assoc($bResult);
$bFrom = $bRow['balance'];
if ($bFrom < $amount) {
echo "<br>insufficient balance to transfer";
return false;
}
$sql = "update payments set balance = balance - $amount where id = $from";
$result = mysqli_query($conn, $sql);
if (!$result) {
echo "<br>Money deduction from account $from failed";
return false;
}
echo "<br>Money deducted from account id $from";
if (!ifExists($to)) {
echo "<br>Account $to does not exist";
return false;
}
$sql = "update payments set balance = balance + $amount where id = $to";
$result = mysqli_query($conn, $sql);
if (!$result) {
echo "<br>Money credit to account $to failed";
return false;
}
echo "<br>Money credited to account id $to";
return true;
}Code language: PHP (php)Let’s understand the above code step by step.
First, we check if the sender exists. If it doesn’t exist, we will print the message and return the false, and will not proceed to further code.
If the sender exists, we will check if the sender’s balance is enough to transfer. If the amount to be transferred is greater than the available balance, we will print a message and return the false to avoid further execution.
If enough balance is available, we will update the balance of the sender by deducting the amount from the current balance. If the update operation fails, we will print a message and return the false to avoid further execution.
Now, we will check if the receiver exists. If it doesn’t exist, we will print the message and return the false, and will not proceed to further code.
we will update the balance of the receiver by adding the amount to the current balance. If the update operation fails, we will print a message and return the false to avoid further execution.
Finally, if the control goes to the end of the function, then it means all SQL statements were executed successfully and we can commit the changes.
If any problem occurs, we return false every time to tell the function call that some problem is occurred and roll back to the original state.
Now, let’s write a function call to the above function.
if (transfer(2, 1, 100)) {
mysqli_commit($conn);
echo "<br>Transfer Successful";
} else {
mysqli_rollback($conn);
echo "<br>Transfer Failed";
}Code language: PHP (php)As you can see here, if the transfer function returns true, we will commit all the changes using the mysqli_commit() method. On the other hand, if we receive a false from the function, we will roll back to the original state using the mysqli_rollback() method.
Testing the PHP-MySQL Transaction Program
Let’s now run a few tests to check if the transactions execute successfully.
Test 1: Transaction Successful
Let’s run the above program where we will send the money from id 2 to id 1.
The function call will look like this-
if (transfer(2, 1, 100)) {
mysqli_commit($conn);
echo "<br>Transfer Successful";
} else {
mysqli_rollback($conn);
echo "<br>Transfer Failed";
}
Code language: PHP (php)Let’s check the output in the browser as well as on the MySQL CLI to see if values get updated.
As you can see, we are getting details of how SQL statements got executed.
Let’s check the table to see if the balances are updated.
As you can see, the balance of id 1 is increased and id 2 is reduced by 100.
Test 2: Transaction Fail- Transfer to Non-Existing Account
Now, we will send the money to the account id that doesn’t exist.
if (transfer(1, 5, 200)) {
mysqli_commit($conn);
echo "<br>Transfer Successful";
} else {
mysqli_rollback($conn);
echo "<br>Transfer Failed";
}
Code language: PHP (php)When the transfer function gets called, it will first check if the sender exists. In this case, the sender exists, so the balance will get deducted from the sender’s account.
However, the receiver doesn’t exist. So, it will return false.
The transfer function returns the false which will cause the rollback() function to invoke.
Let’s run the program and see the flow of execution. Finally, we should get the result as the transfer failed.
As you can see, the amount was deducted from the sender’s account. But due to the absence of the receiver, the transfer failed and the balance remains the same.
Here you can see, that there is no change in balance.
Conclusion
Today we have learned about the PHP-MySQL transaction and how to implement it using a real-life example. It is very important to keep your database consistent in all scenarios. You should focus and practice more about the MySQL transaction as it will help you a lot in future.