Use MySQL Transaction in PHP

In this article I explain how to use transactions with PHP.
  • 1950

Introduction

In the real world, a database may be used by several users at the same time. When it is, then what happens is that two users might try to update a single record at the same time. So this article describes how to handle this type of situation.

Now first I will explain what a is transaction is. "A transaction is a group of SQL statements that you combine into a single unit of work., By combining SQL statements you can prevent certain kinds of database errors." Before you begin using MySQL to work with transactions, you should realize that some storage engines do not support transactions, like the MyISAM storage engine. Only the InnoDB storage engine supports transactions. It is fully ACID compliant. ACID stands for, Atomicity, Consistency, Isolation and Durability. To work with transactions, the preceding property should have use, when you work with transactions. Operation within a transaction must be atomic, which means that either all operations succeed or all fail. The consistency property means, the database must be in a consistent state after the transaction. The data is valid and there are no half-finished records. Isolation is the requirement that other operations cannot access data that has been modified during a transaction that has not yet completed. The question of isolation occurs in case of concurrent transactions. Without isolation, the data may end up in a inconsistent state. Durability is the ability of the database system to recover the committed transaction updates against any kind of system failure.

Example of transaction in PHP

<?
php
$
dbConnection = mysqli_connect('localhost', 'root', '', 'mysql');
mysqli_autocommit($dbConnection, false);
$flag = true;
$query1 = "insert into buyer values(1234567,45345)";
$query2 = "insert into seller values(4357657,1233)";
$result = mysqli_query($dbConnection, $query1);
if (!$result)
{
$flag = false;
echo "Error details: " . mysqli_error($dbConnection) . ". ";
}
$result = mysqli_query($dbConnection, $query2);
if (!$result)
{
$flag = false;
echo "Error details: " . mysqli_error($dbConnection) . ". ";
}
if ($flag)
{
mysqli_commit($dbConnection);
echo "All queries were executed successfully";
}
else
{
mysqli_rollback($dbConnection);
echo "All queries were rolled back";
}
print "<h2>MySQL: All data of seller and buyer table</h2>";
$con = mysql_connect("localhost","root","");
if (!$con)
{
die('Could
not connect: ' . mysql_error());
}
mysql_select_db("mysql", $con);
$result1 = mysql_query("SELECT * FROM buyer");
$result2 = mysql_query("SELECT * FROM seller");
echo "<table
border='1'><tr>;
<th>
AccountNumber</th>;
<th>
Amount</th>;
</tr>
";
while($rowval = mysql_fetch_array($result1))
{
echo "
<tr>";
echo "
<td>" . $rowval['account_id'] . "</td>";
echo "
<td>" . $rowval['amount'] . "</td>";
echo "
</tr>";
}
while($rowval = mysql_fetch_array($result2))
{
echo "
<tr>";
echo "
<td>" . $rowval['account_id'] . "</td>";
echo "
<td>" . $rowval['amount'] . "</td>";
echo "
</tr>";
}
echo "
</table>";
mysql_close($con);
mysqli_close($dbConnection);

?>

Output

 transction-in-php.jpg

© 2019 DotNetHeaven. All rights reserved.