Posted in Codeigniter

CodeIgniter 4 MySQL 8 CRUD Example

Introduction

Codeigniter 4 MySQL 8 CRUD example will show you how you can build an application that performs CRUD operations using PHP based framework Codeigniter. CRUD is an acronym that stands for Create Read Update and Delete operations.

It’s almost in every application that has user management system will need basic CRUD operations.

Related Posts

Prerequisites

Codeigniter 4, MySQL 8, PHP 7.4, Apache HTTP Server 2.4

Project Directory

It’s assumed that you have already setup PHP and CodeIgniter in Windows system. Now I will create a project root directory called codeigniter-mysql-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 a table called 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(100) COLLATE utf8mb4_unicode_ci NOT NULL,
	`price` double COLLATE utf8mb4_unicode_ci NOT NULL,
	`sale_price` double COLLATE utf8mb4_unicode_ci NOT NULL,
	`sales_count` int unsigned COLLATE utf8mb4_unicode_ci NOT NULL,
	`sale_date` VARCHAR(20) NOT NULL,
	PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

I am also going to store sample data into the product table to test the application right away.

insert into product(id,name,price,sale_price,sales_count,sale_date) values(1, 'Desktop','30000','35000','55','02-04-2018');
insert into product(id,name,price,sale_price,sales_count,sale_date) values(2, 'Desktop','30300','37030','43','03-04-2018');
insert into product(id,name,price,sale_price,sales_count,sale_date) values(3, 'Tablet','39010','48700','145','04-04-2018');
insert into product(id,name,price,sale_price,sales_count,sale_date) values(4, 'Phone','15000','17505','251','05-04-2018');
insert into product(id,name,price,sale_price,sales_count,sale_date) values(5, 'Phone','18000','22080','178','05-04-2018');
insert into product(id,name,price,sale_price,sales_count,sale_date) values(6, 'Tablet','30500','34040','58','05-04-2018');
insert into product(id,name,price,sale_price,sales_count,sale_date) values(7, 'Adapter','2000','2500','68','06-04-2018');
insert into product(id,name,price,sale_price,sales_count,sale_date) values(8, 'TV','45871','55894','165','07-04-2018');

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 CRUD (Create, Read, Update, Delete) operations on the product table. The following source code is written into app/Models/ProductModel.php file. So I will perform insert, fetch, update and delete operations using different functions in the model class.

In this model class I will create different functions for performing CRUD operations. These functions will be called from controller class. I am not going to perform any database operations from controller class. 

<?php

namespace App\Models;
use CodeIgniter\Model;

class ProductModel extends Model {
	
	protected $product = 'product';
	
	function get_product($id) {        
		$query = $this->db->table($this->product)->where('id', $id)->get();
        
		return $query->getRow();
    }
	
	function get_product_list() {        
		$query = $this->db->table($this->product)->get();
        
		return $query->getResult();
    }
	
	function save_product_info($data) {
		$this->db->table($this->product)->insert($data);
	}
	
	function update_product_info($data) {
		$this->db->table($this->product)->replace($data);
		//$this->db->table($this->product)->where('id', $data['id'])->update($data);
	}
	
	function delete_product_info($id) {
		$this->db->table($this->product)->where('id', $id)->delete();
	}
	
}

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/ProductController.php.

<?php

namespace App\Controllers;

use App\Models\ProductModel;

class ProductController extends BaseController {
	
	public function index()	{
		$model = new ProductModel();
		
		$data['product_list'] = $model->get_product_list();
		
		return view('products', $data);
	}
	
	public function create()	{
		helper(['form', 'url']);

		if($this->request->getPost('submit')) {
			$validation =  \Config\Services::validation();
			
			$validation->setRules([
				'name' => ['label' => 'Name', 'rules' => 'required|min_length[3]|max_length[30]'],
				'price' => ['label' => 'Price', 'rules' => 'required|decimal'],
				'sale_price' => ['label' => 'Selling Price', 'rules' => 'required|decimal'],
				'sales_count' => ['label' => 'Sales Count', 'rules' => 'required|integer'],
				'sale_date' => ['label' => 'Selling Date', 'rules' => 'required|valid_date']
			]);
			
			if (!$validation->withRequest($this->request)->run()) {
				echo view('product_add', ['errors' => $validation->getErrors()]);
			} else {
				$model = new ProductModel();
				
				$data = array(
					'name' => $this->request->getPost('name'),
					'price' => $this->request->getPost('price'),
					'sale_price' => $this->request->getPost('sale_price'),
					'sales_count' => $this->request->getPost('sales_count'),
					'sale_date' => $this->request->getPost('sale_date')
				);
				
				$model->save_product_info($data);
				
				return redirect()->to(site_url());
			}
		} else {
			echo view('product_add');
		}
	}
	
	public function update()	{
		helper(['form', 'url']);

		if($this->request->getPost('submit')) {
			$validation =  \Config\Services::validation();
			
			$validation->setRules([
				'id' => ['label' => 'Id', 'rules' => 'required'],
				'name' => ['label' => 'Name', 'rules' => 'required|min_length[3]|max_length[30]'],
				'price' => ['label' => 'Price', 'rules' => 'required|decimal'],
				'sale_price' => ['label' => 'Selling Price', 'rules' => 'required|decimal'],
				'sales_count' => ['label' => 'Sales Count', 'rules' => 'required|integer'],
				'sale_date' => ['label' => 'Selling Date', 'rules' => 'required|valid_date']
			]);
			
			if (!$validation->withRequest($this->request)->run()) {
				echo view('product_add', ['errors' => $validation->getErrors()]);
			} else {
				$model = new ProductModel();
				
				$data = array(
					'id' => $this->request->getPost('id'),
					'name' => $this->request->getPost('name'),
					'price' => $this->request->getPost('price'),
					'sale_price' => $this->request->getPost('sale_price'),
					'sales_count' => $this->request->getPost('sales_count'),
					'sale_date' => $this->request->getPost('sale_date')
				);
				
				$model->update_product_info($data);
				
				return redirect()->to(site_url());
			}
		} else if($this->request->getGet('id')) {
			$model = new ProductModel();
			
			$data['product_info'] = $model->get_product($this->request->getGet('id'));
			
			echo view('product_update', $data);
		} else {
			return redirect()->to(site_url());
		}
	}
	
	public function delete_product() {
		
		if($this->request->getGet('id')) {
			$model = new ProductModel();
			$model->delete_product_info($this->request->getGet('id'));
			//$model->where('id', $id)->delete();
		}
		
		return redirect()->to(site_url());
	}
	
}

There are four functions for performing CRUD operations.

View File

The view or template file is responsible for displaying data to the end user.

List Of Products

List of products is shown using the view file – app/Views/products.php.

<!DOCTYPE html>
<html lang="en">
<head>
	<meta charset="UTF-8">
	<title>CodeIgniter 4 and MySQL 8 CRUD Example - List of Products</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"/>

	<!-- STYLES -->

	<style>
	#products {
	  font-family: Arial, Helvetica, sans-serif;
	  border-collapse: collapse;
	  width: 100%;
	}

	#products td, #products th {
	  border: 1px solid #ddd;
	  padding: 8px;
	}

	#products tr:nth-child(even){background-color: #f2f2f2;}

	#products tr:hover {background-color: #ddd;}

	#products th {
	  padding-top: 12px;
	  padding-bottom: 12px;
	  text-align: left;
	  background-color: black;
	  color: white;
	}
	</style>
</head>
<body>

<div>
	<h1>CodeIgniter 4 and MySQL 8 CRUD Example</h1>

	<div id="body">
		<p><a href="<?php echo site_url('productcontroller/create') ?>">Add New Product</a></p>
	
		<?php
			if ($product_list) {
		?>
        <table id="products">
            <thead>
				<tr>
					<th>Id</th>
					<th>Name</th>
					<th>Price</th>
					<th>Sale Price</th>
					<th>Sale Count</th>
					<th>Sale Date</th>
					<th>Actions</th>
                </tr>
            </thead>
			<tbody>
				<?php
					foreach ($product_list as $product) {
					?>
					<tr>
						<td>
							<?php echo $product->id; ?>
						</td>
						<td>
							<?php echo $product->name; ?>
						</td>
						<td>
							<?php echo $product->price; ?>
						</td>
						<td>
							<?php echo $product->sale_price; ?>
						</td>
						<td>
							<?php echo $product->sales_count; ?>
						</td>
						<td>
							<?php echo $product->sale_date; ?>
						</td>
						<td>
							<a href="<?php echo site_url('productcontroller/update?id=' . $product->id) ?>">Edit</a>&nbsp;|&nbsp;<a onclick="return confirm('Delete Product with id - <?php echo $product->id; ?>?')" href="<?php echo site_url('productcontroller/delete_product?id=' . $product->id) ?>">Delete</a>
						</td>
					</tr>
					<?php
				}
				?>
			</tbody>
        </table>
    <?php
        } else {
            echo '<div style="color:red;"><p>No Record Found!</p></div>';
        }
    ?>
	</div>
</div>

</body>
</html>

The above file will display data in tabular format.

Add New Product

The following code is written in a file called app/Views/product_add.php. All fields are mandatory and you will get error if you do not input any one of them. 

<!DOCTYPE html>
<html>
<head>
    <title>CodeIgniter 4 and MySQL 8 CRUD Example - Add Product</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;">
		<p><a href="<?php echo site_url('productcontroller') ?>">List of 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('productcontroller/create') ?>

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

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

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

			<h5>Sales Count</h5>
			<input type="text" name="sales_count" value="<?php echo set_value('sales_count'); ?>" size="50" />
			
			<h5>Selling Date</h5>
			<input type="text" name="sale_date" value="<?php echo set_value('sale_date'); ?>" size="50" />

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

		</form>
	</div>

</body>
</html>

Update Existing Product

The view file is created for updating the existing product at app/Views/product_update.php. The product id field is readonly on the update form because id will never be changed. 

<!DOCTYPE html>
<html>
<head>
    <title>CodeIgniter 4 and MySQL 8 CRUD Example - Update Product</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;">
		<p><a href="<?php echo site_url('productcontroller') ?>">List of Products</a>&nbsp;|&nbsp<a href="<?php echo site_url('productcontroller/create') ?>">Add New Product</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('productcontroller/update') ?>
			<h5>Id</h5>
			<input type="text" name="id" readonly value="<?php echo $product_info->id; ?>"/>

			<h5>Name</h5>
			<input type="text" name="name" value="<?php echo set_value('name') !=null ? set_value('name') : $product_info->name; ?>" size="50" />

			<h5>Price</h5>
			<input type="text" name="price" value="<?php echo set_value('price') != null ? set_value('price') : $product_info->price; ?>" size="50" />

			<h5>Selling Price</h5>
			<input type="text" name="sale_price" value="<?php echo set_value('sale_price') !=null ? set_value('sale_price') : $product_info->sale_price; ?>" size="50" />

			<h5>Sales Count</h5>
			<input type="text" name="sales_count" value="<?php echo set_value('sales_count') != null ? set_value('sales_count') : $product_info->sales_count; ?>" size="50" />
			
			<h5>Selling Date</h5>
			<input type="text" name="sale_date" value="<?php echo set_value('sale_date') != null ? set_value('sale_date') : $product_info->sale_date; ?>" size="50" />

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

		</form>
	</div>

</body>
</html>

In the above update form I am setting either existing product information or the input values.

Delete Existing Product

For deleting the product you don’t need a separate view file. So clicking on the Delete link on each row on the table will ask for confirmation and call the controller function for deleting the product from the database table.

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('ProductController');.

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

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

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.

Codeigniter 4 CRUD – Read

The URL http://localhost:8080/ will show you the following page on the browser with a list of products.

codeigniter 4 mysql 8 crud - read

In the above page for each row you will find two buttons for updating the existing product information and deleting the existing product information.

To add new product information you can click on the Add New Product link above the table.

Codeigniter 4 CRUD – Add

The below image displays a form where you can input product information to add a new product. You will also find a link on top of the form labelled with List of Products which will take you to the product list page. All fields are mandatory and leaving any input field empty will give you error.

Once a new product added successfully, you will be taken to the product list page.

codeigniter 4 mysql 8 crud - add operation

Codeigniter 4 CRUD – Update

You need to click on the Edit link to update the existing product information.

codeigniter 4 mysql 8 crud - edit operation

Your update page will be auto populated with the product information and you can change the existing values.

In this page you will see two links – one is for going back to product list page and another one for adding new product.

Once a product is updated then you will be redirected to the product list page.

codeigniter 4 mysql 8 crud - update operation

Codeigniter 4 CRUD – Delete

You need to click on the Delete link to delete a product. You will be asked for confirmation before deletion.

codeigniter 4 mysql 8 crud - delete operation

The confirmation alert box appears and you need to click Ok to delete and cancel to remain on the same page without deletion.

codeigniter 4 mysql 8 crud - delete confirm alert

Once successfully deleted you will be redirected to the product list page.

codeigniter 4 mysql 8 crud example

Hope you got an idea how to build CodeIgniter 4 MySQL 8 CRUD example.

Source Code

Download

Leave a Reply

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