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> | <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> | <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.
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 CRUD – Update
You need to click on the Edit link to update the existing product information.
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 CRUD – Delete
You need to click on the Delete link to delete a product. You will be asked for confirmation before deletion.
The confirmation alert box appears and you need to click Ok to delete and cancel to remain on the same page without deletion.
Once successfully deleted you will be redirected to the product list page.
Hope you got an idea how to build CodeIgniter 4 MySQL 8 CRUD example.