Codeigniter transaction example will show you how we can use transaction in Codeigniter. We know that transaction is an important part of persistence storage media because we may save, update or delete data from persistence storage area.

For example, in database we may need to update multiple tables or we may require to delete from multiple tables, hence we may face some inconsistency during database tables updation or deletion from database tables. 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, we want either a unit of work will be carried out completely or none. Hence the codeigniter transaction is required to make it happen consistently.
For information on Codeigniter transaction please go through https://ellislab.com/codeigniter/user-guide/database/transactions.html

Prerequisites

Knowledge of PHP & Codeigniter
MySQL 5.x
Apache HTTP server 2.2
Codeigniter 2.1.4

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

Step 1. Create MySQL tables and these tables will be used for updation or deletion of data. We have created two tables here for this codeigniter transaction example.

The user_account table holds the information about user’s login details whereas user_info holds the personal information about a user. Here we have maintained a foreign key in the user_info table.

CREATE TABLE `user_account` (
    `account_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `account_login` varchar(25) COLLATE latin1_general_ci NOT NULL,
    `account_password` varchar(255) COLLATE latin1_general_ci NOT NULL,
    `user_name` varchar(30) COLLATE latin1_general_ci NOT NULL,
    `user_email` varchar(100) COLLATE latin1_general_ci NOT NULL,
    `last_login` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`account_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
CREATE TABLE `user_info` (
    `user_info_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `user_first_name` varchar(25) COLLATE latin1_general_ci NOT NULL,
    `user_last_name` varchar(20) COLLATE latin1_general_ci NOT NULL,
    `user_address` varchar(255) COLLATE latin1_general_ci NOT NULL,
    `accound_id` int(10) 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=latin1 COLLATE=latin1_general_ci;

Step 2. Create usermodel.php file under application/models directory for updating data into database or deleting data from database. In the below codeigniter transaction example we update the few columns in the both tables.

We have last_login column in user_account table and we update first_name, last_name and address in account_info table. So we want to update both tables as a single unit of work. Therefore we begin transaction and update both tables and make the transaction complete. At the end we check for transaction status and accordingly we 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.

<?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 */

That’s all. Thanks for reading.

Tags:

I am a professional Web developer, Enterprise Application developer, Software Engineer and Blogger. Connect me on JEE Tutorials | TwitterFacebook Google PlusLinkedin | Reddit

9 thoughts on “Codeigniter Transaction Example

  1. Hello Soumitra,
    Thanks for all your examples

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

  2. 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. 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

  3. 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.

Leave a Reply

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