Codeigniter Transaction Example

here I am going to explain you how to work with transaction in Codeigniter framework or how we can use transaction in Codeigniter based web application. You know that 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 statement 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.11, MySQL 8.0.17, PHP 7.4.3

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. At 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 on using transaction.

That’s all. Thanks for reading.

9 Thoughts to “Codeigniter Transaction Example”

  1. Abel

    Haiii…
    it’s very amazing…
    your example code solve my problem :)
    I want to say,
    Big thanks to you…

  2. junie

    Sis,

    I’m new in php and ci but I’m still learning from online resources. Can you create a full tutorial in CRUD using transaction. Thanks for helping :)

    1. You need transaction on write, update and delete but not in read. So in this example you will find transactions on update and delete, similarly you can apply on write. Thanks.

  3. Devesh Tiwari

    Hello,

    What if I need to use the delete function somewhere inside the update function?
    I mean how to use a transaction enabled function inside another transaction enabled function. I tried and it fails.

    1. why do you need to use another transaction enabled function inside other and if you need to use a function from transaction enabled function then why do you need to make the called function transactional?

      1. Devesh Tiwari

        Hi,
        Thanks for your attention. Let me explain.

        Suppose there is a function that creates new “Task” that belongs to a particular “Project”. We have a form that submits the task details the user who is assigned to it, and stores task details to task_table and some other tables that stores the relations between task and project or the users table that are assigned to this task for example. So I put it in the transaction rollback. This is independent now.

        When I create a new project, it may be needed to store the project id to some third table. Also I need to create some default tasks that will be related to this particular project. Now the project function has multiple transactions. It will rollback the independent task query but the project will not be rolled back and we have created a project without those default tasks.

        I hope you got my point.

        Thanks again,
        Devesh

      2. you can put different section of function in transaction boundary and rollback according to your needs.

  4. hemanthraj

    Hello Soumitra,
    Thanks for all your examples

    Is there any continuation for the above tutorial..? if yes please help us in CI itself.

Leave a Comment