Introduction

Here I am going to explain how to work with transaction in Codeigniter 3 framework or how you can use transaction in Codeigniter based web application. Transaction is an important part of persistence storage system because you may save, update or delete data from persistence storage any time. When you perform execution of multiple SQL statements across different database or tables or any external system then it is recommended to use transaction otherwise your data may be saved in an inconsistent state.

For example, in database you may need to update multiple tables or you may require to delete from multiple tables, hence you may face some inconsistency issue during database table updation. During updation few tables may be updated and few tables may not be updated. Similarly, in case of deletion few tables may get deleted and few may not. So data will be in inconsistent state.

Therefore, you want either a unit of work will be carried out completely or none. Hence the Codeigniter transaction is required to make it happen consistently or atomically.

Prerequisites

CodeIgniter 3.1.10 – 3.1.11, MySQL 8.0.17 – 8.0.26, PHP 7.4.3 – 7.4.22

CodeIgniter Transaction

Now look at the below steps how Codeigniter transaction works in PHP technology.

MySQL Table

Create MySQL tables and these tables will be used for updation or deletion of data. I have created two tables in MySQL server for this Codeigniter transaction example.

The user_account table contains the information about user’s login details whereas user_info contains the personal information about a user.

Here I have maintained a foreign key in the user_info table.

CREATE TABLE `user_account` (
    `account_id` int unsigned NOT NULL AUTO_INCREMENT,
    `account_login` varchar(25) COLLATE utf8mb4_unicode_ci NOT NULL,
    `account_password` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
    `user_name` varchar(30) COLLATE utf8mb4_unicode_ci NOT NULL,
    `user_email` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
    `last_login` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`account_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `user_info` (
    `user_info_id` int unsigned NOT NULL AUTO_INCREMENT,
    `user_first_name` varchar(25) COLLATE utf8mb4_unicode_ci NOT NULL,
    `user_last_name` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
    `user_address` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
    `accound_id` int unsigned NOT NULL,
    PRIMARY KEY (`user_info_id`),
    KEY `fk_account_id` (`accound_id`),
    CONSTRAINT `fk_account_id` FOREIGN KEY (`accound_id`) REFERENCES `user_account` (`account_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Model Class

Model class in Codeigniter framework is responsible for performing any database activities. So create usermodel.php file under application/models directory for updating data into database or deleting data from database.

In the following model class, Codeigniter transaction example, you need to update few columns in the both tables.

<?php

if (!defined('BASEPATH'))
exit('No direct script access allowed');

/**
* Description of usermodel
*
* @author https://roytuts.com
*/
class usermodel {

    private $user_account = 'user_account';
    private $user_info = 'user_info';

    /**
    * Update user_account and user_info tables
    * @param type $user_account_id
    * @param type $user_first_name
    * @param type $user_last_name
    * @param type $user_address
    * @return boolean
    */
    function update_user_info($user_account_id, $user_first_name, $user_last_name, $user_address) {
        //which columns need to be updated
        $user_info_data = array(
            'user_first_name' => $this->db->escape_like_str($user_first_name),
            'user_first_name' => $this->db->escape_like_str($user_last_name),
            'user_address' => $this->db->escape_like_str($user_address)
        );
        
		//which columns need to be updated
        $user_acc_data = array(
            'last_login' => date('Y-m-d')
        );
        
		//start the transaction
        $this->db->trans_begin();
        
		//update user_account table
        $this->db->where('user_account_id', $user_account_id);
        $this->db->update($this->user_account, $user_acc_data);
        
		//update user_info table
        $this->db->where('account_id', $user_account_id);
        $this->db->update($this->user_info, $user_info_data);
        
		//make transaction complete
        $this->db->trans_complete();
        
		//check if transaction status TRUE or FALSE
        if ($this->db->trans_status() === FALSE) {
            //if something went wrong, rollback everything
            $this->db->trans_rollback();
			return FALSE;
        } else {
            //if everything went right, commit the data to the database
            $this->db->trans_commit();
            return TRUE;
        }
    }

    /**
    * Delete from user_account and user_info
    * @param type $user_account_id
    * @param type $user_first_name
    * @param type $user_last_name
    * @param type $user_address
    * @return boolean
    */
    function delete_user_info($user_account_id, $user_first_name, $user_last_name, $user_address) {
        //start the transaction
        $this->db->trans_begin();
        
		//delete user_account table
        $this->db->where('user_account_id', $user_account_id);
        $this->db->delete($this->user_account);
        
		//delete user_info table
        $this->db->where('account_id', $user_account_id);
        $this->db->delete($this->user_info);
        
		//make transaction complete
        $this->db->trans_complete();
		
        //check if transaction status TRUE or FALSE
        if ($this->db->trans_status() === FALSE) {
            //if something went wrong, rollback everything
            $this->db->trans_rollback();
			return FALSE;
        } else {
            //if everything went right, delete the data from the database
            $this->db->trans_commit();
            return TRUE;
        }
    }

}

/* End of file usermodel.php */
/* Location: ./application/models/usermodel.php */

You have last_login column in user_account table and you update first_name, last_name and address in account_info table. So you want to update both tables as a single unit of work. Therefore you need to begin transaction and update both tables and make the transaction complete. In the end you will check for the transaction status and accordingly you commit or rollback.

You may also delete or insert into multiple tables using the Codeigniter transaction.

This is a simple example and the real application should have more complex transaction boundary.

Here you can find an example about Codeigniter transaction.

That’s all about Codeigniter transaction.