How to implement commit and rollback in PHP and MySQL

If you want to do multiple database operations as a single transaction in PHP, then you must use commit and rollback functions of MySQL within PHP. This is done to make sure all database operations applicable for the transaction are either committed or rolled back. This means if any of the database operations fails, then all database operations attached to the transaction should be rolled back. Otherwise, it will result in inconsistent data in the database.

In this topic, I will develop an application in PHP and MySQL, to implement commit and rollback, where users can submit a form to register their skill details. One database insert is dependent on successful completion of the another insert operation. By default, MySQL works in autocommit enabled mode.

To disable autocommit, we can use below statement. Here $conn is my MySQL database connection pointer.

To use explicit commit and rollback, we can use below statements:

Using these features, I will develop an application where I will use explicit commit and rollback statements.

commit transaction mysqlFolders and Files

Below is a screenshot of the folder structure and files I am using:

PHP mysqli commit() Function

I have created a folder called 'transaction' under 'htdocs' as I am using XAMPP. If you are using WAMP, create 'transaction' folder under 'www'. Not necessarily you have to create 'transaction' folder, you can use any folder you want.

Folder 'cfg' - In this folder I have kept dbconnect.php which is used to connect to the MySQL database.
Folder 'css' - My custom stylesheet is in this folder.
Folder 'db' - In this folder I have kept my create table scripts.

I have two php programs, one is index.php which is a form to take input data from user and other is process_skills.php which is used to process the data submitted by the user. Actually, I have included process_skills.php within index.php. Below is a screenshot of the form:

PHP mysqli rollback()

Step 1 - Create MySQL tables

We will create three tables - skills, candidate and candidate_skills. I have a database called 'demo'. So the tables will be created in this database. If you have an existing database other than demo you can also use it. Just make sure same tables do not exist already. Table structures are given below:

Table 1 - skills

skills table is used to store all skills with their id and name. This is a master table to show the list of available skills. Below is the structure of the table:

ROLLBACK in PHP

This table has 2 columns.

  1. skill_id - it is the primary key and auto incremented
  2. skill_name - name of the skill
Create table script for this table is given below, you can use this code to create the table. You can also download all codes from download section later in this topic.

db/skills.sql

After you create the table, verify if data is inserted correctly, it should have below data in it.

COMMIT and rollback SQL

Table 2 - candidate

candidate table is used to store candidate details like name, email id etc. When user registers with their skills, then candidate details entered by the user are stored in this table. Below is the structure of the table:

commit and rollback in PHP and MySQL

This table has 4 columns.

  1. candidate_id - it is the primary key and auto incremented
  2. name - name of the candidate entered by the user in the form
  3. email_id - email id of the candidate entered by the user in the form
  4. registration_dt - Date on which candidate registered
Create table script for this table is given below, you can use this code to create the table.

db/candidate.sql

I have kept this table empty initially. As users submit the form, this table gets populated.

Table 3 - candidate_skills

candidate_skills table is used to store skills for each candidate. When users register with their skills, then all skills entered by the users are stored in this table, candidate wise. So, one or more rows will be in this table for each candidate. Below is the structure of the table:

PHP and MySQL transaction examples

This table has 3 columns.

  1. id - it is the primary key and auto incremented
  2. candidate_id - candidate_id from candidate table. You can say it is the foreign key from candidate table, though I have not enforced foreign key constraints here.
  3. skill_id - skill_id of the skills for a candidate
Create table script for this table is given below, you can use this code to create the table.

db/candidate_skills.sql

I have kept this table also empty initially. When user submits form, this table gets populated.

Step 2 - Connect to MySQL database (dbconnect.php)

Use below script to connect to the database. Note that we have this database connection php program in 'cfg' folder. This is written once and used in every program where database connection is needed. This will be easy for maintenance and also will enable reusability of the code.

dbconnect.php

I am using mysqli_connect() function which needs 4 parameters.

  1. server - in our case it is localhost
  2. userid - we are using root user
  3. password - no password for user root
  4. database name - demo in our case.
If connection is successful then it will return true and false otherwise. This Boolean value is returned and stored in the $conn variable. We will include this dbconnect.php in other php programs so that we do not need to write it again in the program. For detail database connection understanding please read topic How to Connect to MySQL database using PHP.

Step 3 - Create a form for skill entry (index.php)

Let us create a form using which user can enter name, email id and one or more skills of the user and submit the form. We will have a multi-select input field, where user can select one or more skills from a list. Here, we will enter user details like name and email id in candidate table and user's skills in candidate_skills table. The form looks as below:

Rollback and Commit using MySQLi Transactions

Let's see the code for this.

index.php

For the Skill, we have added a multi-select input, user can choose one or more skills. Remember that values selected in this multi-select list box will be in an array named skill[]. Once the form is submitted, php program process_skills.php will be executed.

Step 4 - Write PHP code to process form data (process_skills.php)

This is the program where we are using commit and rollback explicitly.

It uses one flag to indicate if a database operation is successful or failure. By using this flag, it will determine if it needs to execute commit or rollback. If all inserts are successful, then flag will be true and database commit gets executed. See the code for process_skills.php below:

process_skills.php

Autocommit is disabled using mysqli_autocommit($conn, false). Since skill[] is an array of selected values, for loop is used to insert each skills against the candidate_id in candidate_skills table.

At the end, check if $flag is true, and use mysqli_commit() to commit all inserts.

Step 5 - Add CSS (style.css)

Let us add below styles. I have already added style.css in index.php.

css/style.css

You can see simple styles are added here. Keep this style.css file in your css folder.

Php mysql transaction rollbackTest the Application

Make sure in your XAMPP control panel Apache and MySQL services are running. Open the browser and run localhost/transaction. You will see the form displayed. See the below screen:

implement commit and rollback for MySQL in PHP

Enter Name, Email and select one or more skills and submit. You will see successful message displayed as below:

PHP mysqli_rollback() Function

You should see the candidate details inserted in candidate table and skills are added in candidate_skills table for successful transaction.

For testing rollback, you have to create some scenario so that one of the insert statements fails. I made first insert in candidate table successful, but made skill insert unsuccessful by renaming one of the columns in candidate_skills table (as if some table structure mismatch happened). I got the error and all inserted data were rollbacked. See below error message.

autocommit, Commit, and Rollback

download source code for commit and rollback in PHPDownload Source Code

I have put all codes in a zip file. You can download it by clicking on the Download button below. You do not need to register yourself to download it. You can directly use the code or you can modify them as per your requirements.

MySQL COMMIT and ROLLBACK TutorialConclusion

Commit and Rollback are two critical features for any database transaction. So, while working on multiple database operations (insert, update, delete), you should be very careful so that data inconsistency does not happen. You must know when to use commit and when to use rollback. Hope it will be useful for you, at least a starting point for you.