How to use MySQL Transaction with PHP

Transaction means to complete several actions of a group without any interruption and if something wrong happens then revert everything to the initial stage.
In SQL, successful transaction means that all SQL statements has been executed successfully. If any error occurs, then the data should be a rollback to avoid data inconsistency.
The transaction will not complete unless all operations of that transaction successfully completes, if any, of the operation fails it mean complete transaction fails.
Real Life Example
If you do a transaction of transferring money from one bank to another, at the time if some interruption occur due to internet/server or other issue, then at that time the transaction will rollback to its initial stage and you get your money refunded to your account successfully.

Transactions Properties

There are 4 standard properties :

A) Atomicity : This rollbacks the transaction in case of any failure, so the transaction will be “all or nothing”.
B) Consistency : It ensures that data is successfully updated of all stages after successful commit.
C) Isolation : It ensures that the effects of an incomplete transaction should not even be visible to another transaction. Concurrent execution means that transactions were executed sequentially, means one after the other. Providing isolation is the main goal of concurrency control .
D) Durability : It ensures that results are stored permanently once transaction is committed successfully even if power loss, crashes occurs .

Overview of try & catch block

‘try’ block will be executed at that time when each query written in try block executed successfully, if any, of the query does not work properly then catch block execute and all the transactions will be rollback.
try {
// A set of queries; if one fails, an exception should be thrown
$conn->query(‘CREATE TABLE customer(id int, name varchar(255), amount int)’);
$conn->query(‘INSERT INTO customer VALUES(1,’Customer Name’,10000)’);
} catch (Exception $e) {
}

MySQL transaction in PHP?

1) Make a connection with database by using PHP

$conn = mysqli_connect("localhost", "my_user", "my_password", "dabasename");

2) Check if connection is not established successfully

if (mysqli_connect_errno()) {
printf("Connect failed: %s\n", mysqli_connect_error());
exit();
}

3) Start the transaction by using beginTransaction() method in try block

try {
// First of all, let's begin a transaction
$conn->beginTransaction();
// A set of queries; if one fails, an exception should be thrown
$conn->query('CREATE TABLE customer(id int, name varchar(255), amount int)');
$conn->query('INSERT INTO customer VALUES(1,' Customer Name'',10000)');
} catch (Exception $e) {
}

4) Write SQL query after beginTransaction() method and after execution of SQL query commit() method call in try block

try {
// First of all, let's begin a transaction
$conn->beginTransaction();
// A set of queries; if one fails, an exception should be thrown
$conn->query('CREATE TABLE customer(id int, name varchar(255), amount int)');
$conn->query('INSERT INTO customer VALUES(1,' Customer Name'',10000)');
// If we arrive here, it means that no exception was thrown
// i.e. no query has failed, and we can commit the transaction
$conn->commit();
} catch (Exception $e) {
}

5) If try block doesn’t execute successfully then the transaction will be rollback in catch block by calling rollBack() method

try {
// First of all, let's begin a transaction
$conn->beginTransaction();
// A set of queries; if one fails, an exception should be thrown
$conn->query('CREATE TABLE customer(id int, name varchar(255), amount int)');
$conn->query('INSERT INTO customer (id,name,amount) VALUES(1,' Customer Name'',10000)');
// If we arrive here, it means that no exception was thrown
// i.e. no query has failed, and we can commit the transaction
$conn->commit();
} catch (Exception $e) {
// An exception has been thrown
// We must rollback the transaction
$conn->rollback();
}

MYSQL TRANSACTION in CAKEPHP 3.0

The most basic way of doing transactions is through the begin(), commit() and rollback() methods,

$conn->begin();
$conn->execute('UPDATE table SET status = ? WHERE id = ?', [true, 2]);
$conn->execute('UPDATE table SET status = ? WHERE id = ?', [false, 4]);
$conn->commit();

Hope you enjoyed this article and learned basic knowledge of MySQL TRANSACTIONS. If you need any help in implementing TRANSACTIONS in your web development project, then mail us , our coding expert team will help you to give best services.