In the previous section, we reiterated how important it is to make sure that an update or delete query contain the desirable matching set of rows. Even though this will always apply, there is a way to revert the changes that you just made, which is working with transactions.

A transaction is a state where MySQL keeps track of all the changes that you make in your data in order to be able to revert all of them if needed. You need to explicitly start a transaction, and before you close the connection to the server, you need to commit your changes. This means that MySQL does not really perform these changes until you tell it to do so. If during a transaction you want to revert the changes, you should roll back instead of making a commit.

PDO allows you to do this with three functions:

One possible use of transactions in your application is when you need to perform multiple queries and all of them have to be successful and the whole set of queries should not be performed otherwise. This would be the case when adding a sale into the database. Remember that our sales are stored in two tables: one for the sale itself and one for the list of books related to this sale. When adding a new one, you need to make sure that all the books are added to this database; otherwise, the sale will be corrupted. What you should do is execute all the queries, checking for their returning values. If any of them returns false, the whole sale should be rolled back.

Let's create an addSale function in your init.php file in order to emulate this behavior. The content should be as follows:

function addSale(int $userId, array $bookIds): void {
    $db = new PDO(
        'mysql:host=127.0.0.1;dbname=bookstore',
        'root',
        ''
    );

    $db->beginTransaction();
    try {
        $query = 'INSERT INTO sale (customer_id, date) '
            . 'VALUES(:id, NOW())';
        $statement = $db->prepare($query);
        if (!$statement->execute(['id' => $userId])) {
            throw new Exception($statement->errorInfo()[2]);
        }
        $saleId = $db->lastInsertId();

        $query = 'INSERT INTO sale_book (book_id, sale_id) '
            . 'VALUES(:book, :sale)';
        $statement = $db->prepare($query);
        $statement->bindValue('sale', $saleId);
        foreach ($bookIds as $bookId) {
            $statement->bindValue('book', $bookId);
            if (!$statement->execute()) {
                throw new Exception($statement->errorInfo()[2]);
            }
        }

        $db->commit();
    } catch (Exception $e) {
        $db->rollBack();
        throw $e;
    }
}

This function is quite complex. It gets as arguments the ID of the customer and the list of books as we assume that the date of the sale is the current date. The first thing we will do is connect to the database, instantiating the PDO class. Right after this, we will begin our transaction, which will last only during the course of this function. Once we begin the transaction, we will open a try…catch block that will enclose the rest of the code of the function. The reason is that if we throw an exception, the catch block will capture it, rolling back the transaction and propagating the exception. The code inside the try block just adds first the sale and then iterates the list of books, inserting them into the database too. At all times, we will check the response of the execute function, and if it's false, we will throw an exception with the information of the error.

Let's try to use this function. Write the following code that tries to add a sale for three books; however, one of them does not exist, which is the one with the ID 200:

try {
    addSale(1, [1, 2, 200]);
} catch (Exception $e) {
    echo 'Error adding sale: ' . $e->getMessage();
}

This code will echo the error message, complaining about the nonexistent book. If you check in MySQL, there will be no rows in the sales table as the function rolled back when the exception was thrown.

Finally, let's try the following code instead. This one will add three valid books so that the queries are always successful and the try block can go until the end, where we will commit the changes:

try {
    addSale(1, [1, 2, 3]);
} catch (Exception $e) {
    echo 'Error adding sale: ' . $e->getMessage();
}

Test it, and you will see how there is no message printed on your browser. Then, go to your database to make sure that there is a new sales row and there are three books linked to it.