PHP MySQL Transaction

 PHP MySQL Transaction

Summary: in this tutorial, you will learn how to handle MySQL transactions in PHP to ensure the data integrity of the database.

A transaction is a set of inter-dependent SQL statements that need to execute in all-or-nothing mode. A transaction is successful if all SQL statements are executed successfully. A failure of any statement will trigger the system to roll back to the original state to avoid data inconsistency.

A classic example of a transaction is a money transfer transaction from one bank account to another. It requires three steps:

• Check the balance of the transferred account to see if the amount is sufficient for the transfer.
• If the amount is sufficient, deduct the amount from the balance of the transferred account.
• Add the transfer amount to the balance of the receiving account.

If an error occurs in the second step, the third step should not continue. In addition, if an error occurs in the third step, the second step must be reversed. The amounts of both bank accounts are intact in case of failure or adjusted correctly if the transaction is completed successfully.

MySQL transaction in PHP

When you use PDO to create a connection to the database that supports the transaction, the auto-commit mode is set. It means that every query you issue is wrapped inside an implicit transaction.

Notice that not all storage engines in MySQL support transactions e.g., MyISAM does not support the transaction, however, InnoDB does.

To handle MySQL transactions in PHP, you use the following steps:

1. Start the transaction by calling the beginTransaction() method of the PDO object.
2. Place the SQL statements and the  commit() method call in a try block.
3. Roll back the transaction in the catch block by calling the rollBack() method of the PDO object.

PHP MySQL transaction example

We will create a table named accounts to demonstrate the money transfer between two bank accounts.

First, execute the following statement to create the accounts table:

CREATE TABLE accounts (
    id     INT AUTO_INCREMENT PRIMARY KEY,
    name   VARCHAR (50)    NOT NULL,
    amount DECIMAL (19, 4) NOT NULL
);

Second, insert two rows into the accounts table:

INSERT INTO accounts(name,amount)
VALUES('John',25000),
      ('Mary',95000);

Third, query the accounts table:

SELECT *
  FROM accounts;

Let’s take a look at the following TransactionDemo class:

<?php

/**
 * PHP MySQL Transaction Demo
 */
class TransactionDemo {

    const DB_HOST = 'localhost';
    const DB_NAME = 'classicmodels';
    const DB_USER = 'root';
    const DB_PASSWORD = '';

    /**
     * Open the database connection
     */
    public function __construct() {
        // open database connection
        $conStr = sprintf("mysql:host=%s;dbname=%s", self::DB_HOST, self::DB_NAME);
        try {
            $this->pdo = new PDO($conStr, self::DB_USER, self::DB_PASSWORD);
        } catch (PDOException $e) {
            die($e->getMessage());
        }
    }

    /**
     * PDO instance
     * @var PDO 
     */
    private $pdo = null;

    /**
     * Transfer money between two accounts
     * @param int $from
     * @param int $to
     * @param float $amount
     * @return true on success or false on failure.
     */
    public function transfer($from, $to, $amount) {

        try {
            $this->pdo->beginTransaction();

            // get available amount of the transferer account
            $sql = 'SELECT amount FROM accounts WHERE id=:from';
            $stmt = $this->pdo->prepare($sql);
            $stmt->execute(array(":from" => $from));
            $availableAmount = (int) $stmt->fetchColumn();
            $stmt->closeCursor();

            if ($availableAmount < $amount) {
                echo 'Insufficient amount to transfer';
                return false;
            }
            // deduct from the transferred account
            $sql_update_from = 'UPDATE accounts
				SET amount = amount - :amount
				WHERE id = :from';
            $stmt = $this->pdo->prepare($sql_update_from);
            $stmt->execute(array(":from" => $from, ":amount" => $amount));
            $stmt->closeCursor();

            // add to the receiving account
            $sql_update_to = 'UPDATE accounts
                                SET amount = amount + :amount
                                WHERE id = :to';
            $stmt = $this->pdo->prepare($sql_update_to);
            $stmt->execute(array(":to" => $to, ":amount" => $amount));

            // commit the transaction
            $this->pdo->commit();

            echo 'The amount has been transferred successfully';

            return true;
        } catch (PDOException $e) {
            $this->pdo->rollBack();
            die($e->getMessage());
        }
    }

    /**
     * close the database connection
     */
    public function __destruct() {
        // close the database connection
        $this->pdo = null;
    }

}

// test the transfer method
$obj = new TransactionDemo();

// transfer 30K from from account 1 to 2
$obj->transfer(1, 2, 30000);


// transfer 5K from from account 1 to 2
$obj->transfer(1, 2, 5000);

We open a database connection in the __construct() method and close it in the __destruct() method. In the transfer() method:

• First, we query amount of the transferred account and compare it with the transfer amount to check if the balance of the transferred account is sufficient.
• Second, in case the amount is sufficient, we deduct the transfer amount from the transferred account and add it to the receiving account.
• Third, we commit the transaction by calling the method. If any error occurs, we call the rollBack() method in the catch block to roll back the transaction.

Let’s test the transfer() method.

// transfer 30K from from account 1 to 2
$obj->transfer(1, 2, 30000);

We transferred 30K from John’s account to Mary’s. We got the following message:

Insufficient amount to transfer

Let’s make another transfer:

// transfer 5K from from account 1 to 2
$obj->transfer(1, 2, 5000);

The script returns the following message:

The amount has been transferred successfully.

We have transferred money between two bank accounts successfully.

You can download the source code via the following link:

Download PHP MySQL Transaction Source Code

In this tutorial, we have shown you step by step how to handle MySQL transactions in PHP to ensure data integrity.