Using Transactions in PHP

Transactions… They are an essential part of programming if you are performing dependent database operations. By dependent database operations, I mean something like this scenario:

A) Add a record to table b

B) Add a log for this addition operation

C) Update a record in table c according to this added record in step A

D) Add a log for the update in step C

In the scenario above we are performing addition, logging and update operations in different execution steps using different queries and we want all the steps to be successful. Why do we want this? We want this for data integrity. We want all the steps to succeed or rollback them all if one fails. This way we can maintain consistent/correct/integral records in our database.

It is quite easy to begin and commit transactions in PHP. Although there are different ways to create connections and start transactions, in this post I will do it the object-oriented MySQLi way. Check this post to learn about the basics of connection creation in PHP: Creating MySQL Connections Using PHP.

Observe the simple example below and you should easily understand using transactions in PHP the MySQLi object-oriented way. Don’t forget, we need a try-catch block to catch any exceptions and rollback.

Here $conn is a MySQL connection that has been created in MySQLi object-oriented way. What we do is, first begin a transaction then execute 2 dependent queries and if there are no errors commit the transaction at the end of the try block. If there are any problems with any of the query executions that we catch them in the catch block and then rollback the transaction thereby providing the data integrity in our database.

Hope this helps.
Good Luck,

Leave a Reply

Your email address will not be published. Required fields are marked *