Posted in Codeigniter

CodeIgniter 4 Transaction Example

Introduction

Here I am going to show you an example on CodeIgniter 4 transaction using MySQL database server. 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.

Related Posts:

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.

Traditionally, transactions have required a fair amount of work to implement since they demand that you keep track of your queries and determine whether to commit or rollback based on the success or failure of your queries. This is particularly cumbersome with nested queries. In this situation CodeIgniter framework provides a smart transaction system that does all this for you automatically.

Running Transaction

To run your queries using transactions you will use the $this->db->transStart() and $this->db->transComplete() functions as follows:

$this->db->transStart();
$this->db->query('AN SQL QUERY...');
$this->db->query('ANOTHER QUERY...');
$this->db->query('AND YET ANOTHER QUERY...');
$this->db->transComplete();

You can run as many queries as you want between the start/complete functions and they will all be committed or rolled back based on the success or failure of any given query.

Strict Mode

By default, CodeIgniter runs all transactions in Strict Mode. When strict mode is enabled, if you are running multiple groups of transactions, if one group fails all groups will be rolled back. If strict mode is disabled, each group is treated independently, meaning a failure of one group will not affect any others.

Strict Mode can be disabled as follows:

$this->db->transStrict(false);

Disabling Transactions

Transactions are enabled by default. If you would like to disable transactions you can do so using $this->db->transOff():

$this->db->transOff();

$this->db->transStart();
$this->db->query('AN SQL QUERY...');
$this->db->transComplete();

When transactions are disabled, your queries will be auto-committed, just as they are when running queries without transactions.

Test Mode

You can optionally put the transaction system into “test mode”, which will cause your queries to be rolled back – even if the queries produce a valid result. To use test mode simply set the first parameter in the $this->db->transStart() function to TRUE:

$this->db->transStart(true); // Query will be rolled back
$this->db->query('AN SQL QUERY...');
$this->db->transComplete();

Running Transactions Manually

If you would like to run transactions manually you can do so as follows:

$this->db->transBegin();

$this->db->query('AN SQL QUERY...');
$this->db->query('ANOTHER QUERY...');
$this->db->query('AND YET ANOTHER QUERY...');

if ($this->db->transStatus() === FALSE) {
    $this->db->transRollback();
} else {
    $this->db->transCommit();
}

Now let’s move on to the example for working with transaction in CodeIgniter 4 and MySQL.

Prerequisites

PHP 7.4.3, CodeIgniter 4.1.1, MySQL 8.0.22

Project Directory

It’s assumed that you have setup PHP and CodeIgniter in Windows system.

Now I will create a project root directory called codeigniter-4-mysql-transaction.

Now move all the directories and files from CodeIgniter framework into the project root directory.

I may not mention the project root directory in subsequent sections and I will assume that I am talking with respect to the project root directory.

MySQL Tables

I am going to create two tables – user_account and user_info – under roytuts database in MySQL server. The table creation DDL is given below:

CREATE TABLE `user_account` (
    `account_id` int unsigned COLLATE utf8mb4_unicode_ci NOT NULL AUTO_INCREMENT,
    `user_name` varchar(30) COLLATE utf8mb4_unicode_ci NOT NULL,
    PRIMARY KEY (`account_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `user_info` (
    `info_id` int unsigned COLLATE utf8mb4_unicode_ci NOT NULL AUTO_INCREMENT,
    `full_name` varchar(25) COLLATE utf8mb4_unicode_ci NOT NULL,
    `account_id` int unsigned COLLATE utf8mb4_unicode_ci NOT NULL,
    PRIMARY KEY (`info_id`),
    KEY `fk_account_id` (`account_id`),
    CONSTRAINT `fk_account_id` FOREIGN KEY (`account_id`) REFERENCES `user_account` (`account_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Database Configuration

You need to setup database connection in order to fetch or write data to the table. The following configuration is done in the file app/Config/Database.php under the default group of database setting. Make sure you change or update the configuration according to yours. You can also setup your database using the .env file.

The main properties I have shown below and you can change according to your values:

...
'username' => 'root',
'password' => 'root',
'database' => 'roytuts',
...
'charset'  => 'utf8mb4',
'DBCollat' => 'utf8mb4_unicode_ci',
...

Model

You need a model class to perform database activities, for example, to insert, update and delete user account/information I am using below model class. The below model class is written into app/Models/UserModel.php file.

In CodeIgniter you can choose two ways of modeling your data – using CodeIgniter’s model and using entity class. I am using CodeIgniter’s model to create my own model that extends CodeIgniter’s model class. They come out of the box with helper methods for much of the standard ways you would need to interact with a database table, including finding records, updating records, deleting records, and more.

I am creating my own function in the model class to insert, update and delete data. Here I have used auto transaction start as well as manual transaction start.

<?php

namespace App\Models;
use CodeIgniter\Model;

class UserModel extends Model {

	protected $table_account = 'user_account';
	protected $table_info = 'user_info';
	
	function insert_user($user_name, $full_name) {
		$this->db->transBegin();
		
		$data_account = array(
			'user_name' => $user_name
		);
		
		$this->db->table($this->table_account)->insert($data_account);
		
		$id = $this->db->insertID();
		
		$data_info = array(
			'full_name' => $full_name,
			'account_id' => $id
		);
		
		$this->db->table($this->table_info)->insert($data_info);
		
		if ($this->db->transStatus() === FALSE)	{
			$this->db->transRollback();
			
			return false;
		} else {
			$this->db->transCommit();
			
			return true;
		}
	}
	
	function update_user($account_id, $user_name, $info_id, $full_name) {
		$this->db->transStart();
		
		$data_account = array(
			'user_name' => $user_name
		);
		
		$this->db->table($this->table_account)->where('account_id', $account_id)->update($data_account);
		
		$data_info = array(
			'full_name' => $full_name
		);
		
		$this->db->table($this->table_info)->where('info_id', $info_id)->update($data_info);
		
		$this->db->transComplete();

		if ($this->db->transStatus() === FALSE) {
			return false;
		}
		
		return true;
	}
	
	function delete_user($account_id, $info_id) {
		$this->db->transStart();
		
		$this->db->table($this->table_info)->where('info_id', $info_id)->delete();
		
		$this->db->table($this->table_account)->where('account_id', $account_id)->delete();		
		
		$this->db->transComplete();

		if ($this->db->transStatus() === FALSE) {
			return false;
		}
		
		return true;
	}
	
}

Controller

The following controller (app/Controllers/Home.php) defines the functions for handling user’s request/response and calling the appropriate views and interact with database table to perform the required activities.

In the below model class I am not accepting any input from user but to test the application I am just using some hardcoded values. I am sending success or error message based on the database operations using transaction management.

<?php

namespace App\Controllers;
use App\Models\UserModel;

class Home extends BaseController {
	
	public function index()	{
		$model = new UserModel();
		
		$insert_result = $model->insert_user('roytuts', 'Soumitra');
		
		$update_result = $model->update_user(1, 'roytuts.com', 1, 'Soumitra Roy');
		
		$delete_result = $model->delete_user(1, 1);
		
		$success = ''; $error = '';
		
		if($insert_result) {
			$success .= 'Successfully inserted.';
		} else {
			$error .= 'Something went wrong during insertion.';
		}
		
		if($update_result) {
			$success .= ' Successfully updated.';
		} else {
			$error .= ' Something went wrong during updation.';
		}
		
		if($delete_result) {
			$success .= ' Successfully deleted.';
		} else {
			$error .= ' Something went wrong during deletion.';
		}
		
		return view('welcome_message', ['success' => $success, 'error' => $error]);
	}
	
}

View File

You need to display your data onto a view file. Therefore you will need to create a view file (for example, welcome_message.php) under app/Views folder. In this view I am just showing error or success message.

<!DOCTYPE html>
<html lang="en">
<head>
	<meta charset="UTF-8">
	<title>CodeIgniter 4 MySQL Transaction Example</title>
	<meta name="description" content="The small framework with powerful features">
	<meta name="viewport" content="width=device-width, initial-scale=1.0">
	<link rel="shortcut icon" type="image/png" href="/favicon.ico"/>
</head>
<body>
	
	<div style="width: 600px; margin: auto;">
		<?php
			if(isset($error)):
		?>
			<ul style="list-style: none; color: red;">
				<li><?= esc($error) ?></li>
			</ul>
		<?php
			endif;
		?>
		
		<?php
			if(isset($success)):
		?>
			<ul style="list-style: none; color: green;">
				<li><?= esc($success) ?></li>
			</ul>
		<?php
			endif;
		?>
	</div>
	
</body>
</html>

Testing the Application

I am not going to use any external server but CLI command to run the application. Make sure you start the MySQL database server before you start your application. If you want to use external server to run your application you can use. Execute the following command on your project root directory to run your application.

php spark serve

Your application will be running on localhost and port 8080.

The URL http://localhost:8080/ will show you the following page on the browser.

codeigniter 4 transaction

That’s all how to work with transaction in CodeIgniter 4 and MySQL database.

Source Code

Download

Leave a Reply

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