PHP + MySQL transactions examples

MySLQL transactions can be used when you want to make sure all the statements you specify are executed. Only the tables of InnoDB storage engine support transactions.

In a transaction, if at least one statement fails, all the changes will be rolled back and database will be in its initial state (There are some statements that can not be rolled back: Will be discussed at the end).

In web applications, it’s common that you want to either run set of statements or do no change since success of only few statements can cause data corruption.

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

Suppose we have a process where need to execute 3 queries. If an error occurs in the second step, the third step should not continue and first step must reversed. In addition, if an error occurs in the third step, then first and second step must be reversed.

When you use PDO to create a connection to the MySQL 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. So you can't use MyISAM engine for MySQL if you want transaction support.

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

• Start the transaction by calling the beginTransaction() method of the PDO object.
• Place the SQL statements and the  commit() method call in a try block.
• Rollback the transaction in the catch block by calling the rollBack() method of the PDO object.

Below is a simple PHP script that shows how we can achieve the functionality.

<?php
define("DB_DEFAULT_HOST", "localhost");
define("DB_DEFAULT_DATABASE", "test_database");
define("DB_DEFAULT_USER", "root");
define("DB_DEFAULT_PASSWORD", "");
$dbh = null;
try {
    $dbh = new PDO(
        "mysql:host=".DB_DEFAULT_HOST.";dbname=".DB_DEFAULT_DATABASE,
        DB_DEFAULT_USER,
        DB_DEFAULT_PASSWORD
    );
    $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $dbh->beginTransaction();

    $query = $dbh->prepare("SELECT COUNT(*) as count FROM student WHERE id > :id");
    $query->bindValue("id", 0);
    $query->execute();
    $count = $query->fetch()[0];
    echo "OLD COUNT=$count<br/>";

    $stmt = $dbh->prepare("INSERT INTO student (name,roll) VALUES(?,?)");
    $stmt->execute(['Name', 'Roll']);
    /* Will commit data to database, if you don't call this, data will not save */
    $dbh->commit();

    $count = $dbh->query("SELECT COUNT(*) as count FROM student WHERE id > 0")->fetch()[0];
    echo "NEW COUNT=$count<br/>";

    $students = $dbh->query("SELECT * FROM student WHERE id > 0")->fetchAll(PDO::FETCH_OBJ);
    echo "<pre>"; print_r($students); echo "</pre>";

    /* SEARCH CAPABILITY IN ARRAY DATA BIND */
    $id_list = [1, 2, 3];
    $in = rtrim(str_repeat('?,', count($id_list)), ',');
    $query = $dbh->prepare("SELECT * FROM student WHERE id in ($in)");
    $query->execute($id_list);
    $students = $query->fetchAll(PDO::FETCH_OBJ);
    echo "<pre>"; print_r($students); echo "</pre>"; die();
}
catch (\Exception $ex) {
    echo "MySQL_Error=" . $ex->getMessage();
    $dbh->rollBack();
}

And output is like below:

OLD COUNT=2
NEW COUNT=3
Array
(
    [0] => stdClass Object
        (
            [id] => 1
            [name] => Prtiom First Year
            [roll] => P1
        )

    [1] => stdClass Object
        (
            [id] => 2
            [name] => Pritom Second Year
            [roll] => P2
        )

    [2] => stdClass Object
        (
            [id] => 21
            [name] => Some_Name
            [roll] => 3040
        )

)

Note that, with this idea, if a query fails, an Exception must be thrown:

• PDO can do that, depending on how you configure it
• See PDO::setAttribute
• and PDO::ATTR_ERRMODE and PDO::ERRMODE_EXCEPTION
• You might have to test the result of the function used to execute a query, and throw an exception yourself.

Unfortunately, there is no magic involved. You cannot just put an instruction somewhere and have transactions done automatically: you still have to specific which group of queries must be executed in a transaction.

For example, quite often you'll have a couple of queries before the transaction (before the begin) and another couple of queries after the transaction (after either commit or rollback) and you'll want those queries executed no matter what happened (or not) in the transaction.