Codeigniter 4 MySQL AJAX CRUD Example

Codeigniter 4 CRUD

In this tutorial I am going to show how to build CRUD (Create Read Update Delete) application using Codeigniter 4 and MySQL database using basic AJAX (Asynchronous JavaScript and XML) technique.

I won’t use AJAX technique for all four operations, i.e., fetching (Read), saving (Create), updating and deleting. Out of these four operations, I will use AJAX technique in two operations, i.e., update and delete.

I will show some sample product information on an HTML table and I will also give two buttons (Edit and Delete) for performing update and delete operations. The edit or update operation will be done inline, so you don’t need to navigate to any other page. The delete operation will also be performed inline and before deletion user needs to confirm whether he/she really wants to delete the record.

Prerequisites

PHP 7.4.27, Codeigniter 4.2.11, MySQL 8.0.26

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-mysql-ajax-crud anywhere in the system.

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 Table

For this example I am going to create and insert few sample data into the table product under roytuts database. So create a table product under database roytuts in the MySQL server with the below structure.

CREATE TABLE `product` (
	`id` int unsigned COLLATE utf8mb4_unicode_ci NOT NULL AUTO_INCREMENT,
	`name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
	`code` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
	`price` double COLLATE utf8mb4_unicode_ci NOT NULL,
	PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

I am also adding some sample data into the product table.

INSERT INTO `product` (`id`, `name`, `code`, `price`) VALUES
(1, 'American Tourist', 'AMTR01', 12000.00),
(2, 'EXP Portable Hard Drive', 'USB02', 5000.00),
(3, 'Shoes', 'SH03', 1000.00),
(4, 'XP 1155 Intel Core Laptop', 'LPN4', 80000.00),
(5, 'FinePix Pro2 3D Camera', '3DCAM01', 150000.00),
(6, 'Simple Mobile', 'MB06', 3000.00),
(7, 'Luxury Ultra thin Wrist Watch', 'WristWear03', 3000.00),
(8, 'Headphone', 'HD08', 400.00);

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 Class

The model class will perform single fetch/save/update/delete operations on the product table. The following source code is written into app/Models/ProductModel.php file.

I will perform update operation using update() function specifying the condition using where() function in the model class.

I will perform delete operation using delete() function specifying the condition using where() function in the model class.

<?php

namespace App\Models;
use CodeIgniter\Model;

class ProductModel extends Model {
	
	protected $product = 'product';
	
	function get_product_list() {        
		$query = $this->db->table($this->product)->get();
        
		return $query->getResult();
    }
	
	function delete_product_by_id($id) {		
		if($this->db->table($this->product)->where('id', $id)->delete()) {
			return true;
		} else {
			return false;
		}
	}
	
	function save_product($data) {		
		if($this->db->table($this->product)->insert($data)) {
			return true;
		} else {
			return false;
		}
	}
	
	function update_product($id, $data) {		
		if($this->db->table($this->product)->where('id', $id)->update($data)) {
			return true;
		} else {
			return false;
		}
	}
	
}

Controller Class

The controller class is the entry point for the web application and a controller class handles request and response coming from and going back to clients.

The controller class performs the business logic for the application. The controller class is also responsible for validating, sanitizing, filtering the malformed request data before the data can be processed further for the application requirements.

The code for controller class is written into a file app/Controllers/Product.php for performing read/write/update/delete operations.

<?php

namespace App\Controllers;

use App\Models\ProductModel;

class Product extends BaseController {
	
	public function index()	{
		$model = new ProductModel();
		
		$data['product_list'] = $model->get_product_list();
		
		return view('products', $data);
	}
	
	public function deleteProduct($id = null) {
		if (!empty($id)) {
			$model = new ProductModel();
			
			$results = $model->delete_product_by_id($id);
			
			if ($results === true) {
				echo '<span style="color:green;">Product successfully deleted</span>';
			} else {
				echo '<span style="color:red;">Something went wrong during product deletion</span>';
			}
		} else {
			echo '<span style="color:red;">You must provide product id for deletion</span>';
		}
	}
	
	public function createProduct()	{
		helper(['form']);
		
		return view('product_create');
	}
	
	public function saveProduct() {
		helper(['form', 'url']);

		if($this->request->getPost('save')) {
			$validation =  \Config\Services::validation();
			
			$validation->setRules([
				'name' => ['label' => 'Name', 'rules' => 'required|min_length[3]|max_length[20]'],
				'code' => ['label' => 'Code', 'rules' => 'required|min_length[2]|max_length[10]'],
				'price' => ['label' => 'Price', 'rules' => 'required|decimal']
			]);
			
			if (!$validation->withRequest($this->request)->run()) {
				echo view('product_create', ['errors' => $validation->getErrors()]);
			} else {
				$code = $this->request->getPost('code');
				$name = $this->request->getPost('name');
				$price = $this->request->getPost('price');
				
				$data = array('name' => $name, 'code' => $code, 'price' => $price);
				
				$model = new ProductModel();
			
				$results = $model->save_product($data);
			
				echo view('product_create', ['success' => 'Product successfully saved!']);
			}
		} else {
			echo view('product_create');
		}
	}
	
	public function updateProduct() {
		$id = $this->request->getPost('id');
		$code = $this->request->getPost('code');
		$name = $this->request->getPost('name');
		$price = $this->request->getPost('price');
		
		if (!empty($id) && !empty($code) && !empty($name) && !empty($price)) {
			$data = array('name' => $name, 'code' => $code, 'price' => $price);
			
			$model = new ProductModel();
			
			$results = $model->update_product($id, $data);
			
			if ($results === true) {
				echo '<span style="color:green;">Product successfully updated</span>';
			} else {
				echo '<span style="color:red;">Something went wrong during product updation</span>';
			}
		} else {
			echo '<span style="color:red;">You must provide product details for updation</span>';
		}
	}
	
}

I have included jQuery from CDN link and the required jQuery code from public/product.js file. I have also included CSS file public/table.css to apply some basic style on HTML table. The public folder is the folder where you can put your static resource files, such as, js, css, images. Even you can create folders inside the public folder and keep your files.

I am also applied form validation rules before saving a new product information. If validation fails then the error message will be shown in the same page.

View File

The view file is responsible for displaying information on the web page. I will create two view files – one for displaying a list of products on a page and another one for creating a new product information.

So, I will create a view files products.php under app/Views folder for displaying products.

<!DOCTYPE html>
<html lang="en">
<head>
	<meta charset="UTF-8">
	<title>Codeigniter 4 MySQL AJAX CRUD 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"/>
	
	<link rel="stylesheet" href="table.css"/>
	<script src="https://code.jquery.com/jquery-3.6.3.min.js" integrity="sha256-pvPw+upLPUjgMXY0G+8O0xUf+/Im1MZjXxxgOcBQBXU=" crossorigin="anonymous"></script>
	<script src="product.js"></script>
</head>
<body>
	<div>
	<h1>Products</h1>

	<div id="body">
		<p><a href="<?php echo base_url('product/create'); ?>">Create a New Product</a></p>
		<?php
			if ($product_list) {
		?>
		<div id="msg"></div>
        <table class="datatable">
            <thead>
				<tr>
					<th>ID</th>
					<th>Code</th>
					<th>Name</th>
					<th>Price</th>
					<th>Actions</th>
                </tr>
            </thead>
			<tbody>
				<?php
					$i = 0;
					foreach ($product_list as $p) {
						$col_class = ($i % 2 == 0 ? 'odd_col' : 'even_col');
						$i++;
					?>
					<tr class="<?php echo $col_class; ?>">
						<td><?php echo $p->id; ?></td>
						<td><?php echo $p->code; ?></td>
						<td><?php echo $p->name; ?></td>
						<td><?php echo $p->price; ?></td>
						<td><button class='edit'>Edit</button>&nbsp;&nbsp;<button class='delete' id='<?php echo $p->id; ?>'>Delete</button></td>
					</tr>
					<?php
				}
				?>
			</tbody>
        </table>
		<?php
			} else {
				echo '<div style="color:red;"><p>No Record Found</p></div>';
			}
		?>
		</div>
	</div>
</body>
</html>

I will create a product_create.php file under app/Views folder to save a new product information.

<!DOCTYPE html>
<html lang="en">
<head>
	<meta charset="UTF-8">
	<title>Codeigniter 4 MySQL AJAX CRUD 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"/>
	
	<link rel="stylesheet" href="table.css"/>
	<script src="https://code.jquery.com/jquery-3.6.3.min.js" integrity="sha256-pvPw+upLPUjgMXY0G+8O0xUf+/Im1MZjXxxgOcBQBXU=" crossorigin="anonymous"></script>
	<script src="product.js"></script>
</head>
<body>
	<div id="body">
		<div style="width: 600px; margin: auto;">
			<h1>Add a New Product</h1>
			
			<p><a href="<?php echo base_url(''); ?>">Back to Products</a></p>
			
			<?php
				if(isset($errors)):
			?>
				<ul style="list-style: none; color: red;">
					<?php foreach ($errors as $error) : ?>
					<li><?= esc($error) ?></li>
					<?php endforeach ?>
				</ul>
			<?php
				endif;
			?>
			
			<?php
				if(isset($success)):
			?>
				<ul style="list-style: none; color: green;">
					<li><?= esc($success) ?></li>
				</ul>
			<?php
				endif;
			?>
			
			<?= form_open('product/save') ?>

				<h5>Name</h5>
				<input type="text" name="name" value="<?php echo set_value('name'); ?>" size="50" />

				<h5>Code</h5>
				<input type="text" name="code" value="<?php echo set_value('code'); ?>" size="50" />

				<h5>Price</h5>
				<input type="text" name="price" value="<?php echo set_value('price'); ?>" size="50" />

				<p/>
				<div><input type="submit" name="save" value="Save" /></div>

			</form>
		</div>
	</div>
</body>
</html>

AJAX for Update and Delete

I have used AJAX technique for only update and deleting the row from the HTML table as well as MySQL database. The following code is written into a file called product.js under public folder of the project’s root directory.

$(function() {
	//Delete a row from the table
	$(document).delegate('.delete', 'click', function() { 
		if (confirm('Do you really want to delete record?')) {
			var id = $(this).attr('id');
			var parent = $(this).parent().parent();
			$.ajax({
				url: "http://localhost:8080/index.php/product/delete/" + id,
				cache: false,
				success: function(msg) {
					parent.fadeOut('slow', function() {
						$(this).remove();
					});
					$("#msg").html(msg);
					//location.reload(true)
					/*window.setTimeout(function(){
						window.location.href = "http://localhost:8080/";
					}, 5000);*/
				},
				error: function() {
					$('#msg').html('<span style=\'color:red; font-weight: bold; font-size: 30px;\'>Error deleting record').fadeIn().fadeOut(4000, function() {
						$(this).remove();
					});
				}
			});
		}
	});
	
	//Edit the record
	$(document).delegate('.edit', 'click', function() {
		var parent = $(this).parent().parent();
		
		var id = parent.children("td:nth-child(1)");
		var code = parent.children("td:nth-child(2)");
		var name = parent.children("td:nth-child(3)");
		var price = parent.children("td:nth-child(4)");
		var buttons = parent.children("td:nth-child(5)");
		
		code.html("<input type='text' id='txtCode' value='" + code.html() + "'/>");
		name.html("<input type='text' id='txtName' value='" + name.html() + "'/>");
		price.html("<input type='text' id='txtPrice' value='" + price.html() + "'/>");
		buttons.html("<button id='save'>Save</button>&nbsp;&nbsp;<button class='delete' id='" + id.html() + "'>Delete</button>");
	});
	
	//Save the record
	$(document).delegate('#save', 'click', function() {
		var parent = $(this).parent().parent();
		
		var id = parent.children("td:nth-child(1)");
		var code = parent.children("td:nth-child(2)");
		var name = parent.children("td:nth-child(3)");
		var price = parent.children("td:nth-child(4)");
		var buttons = parent.children("td:nth-child(5)");
		
		//alert(id.html() + code.children("input[type=text]").val() + name.children("input[type=text]").val() + price.children("input[type=text]").val());
		$.ajax({
			method: "post",
			url: "http://localhost:8080/index.php/product/update",
			data: {'id' : id.html(), 'code' : code.children("input[type=text]").val(), 'name' : name.children("input[type=text]").val(), 'price' : price.children("input[type=text]").val()},
			cache: false,
			success: function(msg) {
				$("#msg").html(msg);
				code.html(code.children("input[type=text]").val());
				name.html(name.children("input[type=text]").val());
				price.html(price.children("input[type=text]").val());
				buttons.html("<button class='edit' id='" + id.html() + "'>Edit</button>&nbsp;&nbsp;<button class='delete' id='" + id.html() + "'>Delete</button>");
			},
			error: function() {
				$('#msg').html('<span style=\'color:red; font-weight: bold; font-size: 30px;\'>Error updating record').fadeIn().fadeOut(4000, function() {
					$(this).remove();
				});
			}
		});
	});
});

Apply Style

I have applied some basic style on the HTML table. I have made the header black coloured. I have also applied color on odd or even rows of the HTML table. I transform the lowercase letters to uppercase letters for headers. The following style content is written into a file table.css under the public folder of the project’s root folder.

table.datatable {
	width:100%;
	border: none;
	background:#fff;
}
table.datatable td.table_foot {
	border: none;
	background: #fff;
	text-align: center;
}
table.datatable tr.odd_col {
	background: none;
}
table.datatable tr.even_col {
	background: #ddd;
}
table.datatable td {
	font-size:10pt;
	padding:5px 10px;
	border-bottom:1px solid #ddd;
	text-align: left;
}
table.datatable th {
	text-align: left;
	font-size: 8pt;
	padding: 10px 10px 7px;   
	text-transform: uppercase;
	color: #fff;
	background-color: black;
	font-family: sans-serif;
}

Route Configuration

You also need to configure route to point to your own controller file instead of the default controller that comes with the framework.

Search for the line $routes->setDefaultController('Home'); and replace it by $routes->setDefaultController('Product');.

Search for the line $routes->get('/', 'Home::index'); and replace it by your controller name, for this example, $routes->get('/', 'Product::index');.

These route configurations are done on the file app/Config/Routes.php.

I have also configured routes for saving, updating and deleting as follows:

$routes->get('product/create', 'Product::createProduct');
$routes->post('product/save', 'Product::saveProduct');
$routes->post('product/update', 'Product::updateProduct');
$routes->get('product/delete/(:num)', 'Product::deleteProduct/$1');

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

Testing Codeigniter 4 CRUD Application

Now hit the URL http://localhost:8080 in the browser and you will find the below page:

When you try to delete a record it will ask for confirmation:

When you click on Edit button it will give you input box for updating the existing values and it will also change the Edit button to Save button:

When you click on a Create a New Product link, it will show you create page:

That’s all about Codeigniter 4 AJAX CRUD.

Source Code

Download

Roy Tutorials