CRUD operations are important aspect of web development. In this article I will talk about CRUD operations in Codeigniter 4. For this we have used Mysql database. We have also used ajax to send request to Codeigniter 4 Controller function which makes the website much more efficient. The use of ajax in CRUD operations makes website faster and more responsive that is it make changes to website without reloading the page.
We will make a Products, Category, Subcategory and Image table to keep all kind of products related details, its category, subcategory along with product images. We will also do add, update, delete operations in the table whenever necessary. In short we will build a complete back end of a Ecommerce webiste using Codeigniter, Mysql and ajax where you can add, update and delete products from Mysql database.
The complete process can be divided into following steps:
- Create Products, product_image, catergory and subcategory table.
- Make database connection.
- Design the Products.php controller.
- Build ProductModel.php model.
- Build ProductImageModel.php
- Build CategoryModel.php
- Build SubcategoryModel.php
- Build View to add update and delete.
- Create Routes.
1. Create Products and product_image table.
Create a database with name ecommerce and create following tables under it.
Products table
CREATE TABLE `products` (
`product_id` varchar(10) NOT NULL,
`product_name` varchar(50) NOT NULL,
`category_id` varchar(10) NOT NULL,
`subcategory_id` varchar(10) NOT NULL,
`product_price` int NOT NULL,
`count` int NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
ALTER TABLE `products`
ADD PRIMARY KEY (`product_id`);
COMMIT;
Product images table
CREATE TABLE `product_images` (
`product_id` varchar(10) NOT NULL,
`imagefilepath` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
ALTER TABLE `product_images`
ADD KEY `product_id` (`product_id`);
ALTER TABLE `product_images`
ADD CONSTRAINT `images_ibfk_1` FOREIGN KEY (`product_id`) REFERENCES `product` (`product_id`) ON DELETE CASCADE ON UPDATE CASCADE;
COMMIT;
Category table
CREATE TABLE `category` (
`category_id` varchar(10) NOT NULL,
`category_name` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
ALTER TABLE `category`
ADD PRIMARY KEY (`category_id`);
COMMIT;
Subcategory table
CREATE TABLE `subcategory` (
`category_id` varchar(10) NOT NULL,
`subcategory_id` varchar(10) NOT NULL,
`subcategory_name` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
ALTER TABLE `subcategory` ADD FOREIGN KEY (`category_id`) REFERENCES
`category`(`category_id`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `subcategory`
ADD PRIMARY KEY (`subcategory_id`);
COMMIT;
2.Make database connection.
Database.php
public $default = [
'DSN' => '',
'hostname' => 'localhost',
'username' => 'root',
'password' => 'password',
'database' => 'ecommerce',
'DBDriver' => 'MySQLi',
'DBPrefix' => '',
'pConnect' => false,
'DBDebug' => (ENVIRONMENT !== 'production'),
'charset' => 'utf8',
'DBCollat' => 'utf8_general_ci',
'swapPre' => '',
'encrypt' => false,
'compress' => false,
'strictOn' => false,
'failover' => [],
'port' => 3306,
];
Goto your Project root directory and then app/Config. Open Database.php and make above changes to hostname,username,password and database as shown above.
3. Design the Products.php controller
Products.php
<?php
namespace App\Controllers;
use App\Models\ProductModel;
use App\Models\ProductImageModel;
use App\Models\CategoryModel;
use App\Models\SubcategoryModel;
use CodeIgniter\Controller;
class Products extends BaseController{
public function index(){
$model = new ProductModel();
$imageModel = new ProductImageModel();
$categoryModel = new CategoryModel();
$subcategoryModel = new SubcategoryModel();
$data = [
'title' => 'Products - Browse/Add/Update/Delete',
'products' => $model->paginate(10),
'categories' => $categoryModel->getCategory(),
'subcategories' => $subcategoryModel->getSubcategory(),
'pager' => $model->pager,
];
echo view('templates/admin-header',$data);
echo view('admin/products', $data);
echo view('templates/admin-sidebar');
echo view('templates/admin-footer');
}
public function addproduct(){
$model = new ProductModel();
$imageModel = new ProductImageModel();
$categoryModel = new CategoryModel();
$subcategoryModel = new SubcategoryModel();
$data = [
'title' => 'Products - Browse/Add/Update/Delete',
'products' => $model->paginate(10),
'categories' => $categoryModel->getCategory(),
'subcategories' => $subcategoryModel->getSubcategory(),
'pager' => $model->pager,
];
if ($this->request->getMethod() === 'post' && $this->validate([
'product_id' => 'required',
'product_name' => 'required',
'product_price' => 'required',
"count" => 'required',
"category" => 'required',
"subcategory" => 'required'
]))
{
$model->save([
'product_id' => $this->request->getPost('product_id'),
'product_name' => $this->request->getPost('product_name'),
'category_id' => $this->request->getPost('category'),
'subcategory_id' => $this->request->getPost('subcategory'),
'product_price' => $this->request->getPost('product_price'),
'count' => $this->request->getPost('count')
]);
if($imagefile = $this->request->getFiles())
{
foreach($imagefile['images'] as $img)
{
if ($img->isValid() && ! $img->hasMoved()) {
$newName = $img->getRandomName();
$img->move(WRITEPATH.'uploads', $newName);
$imageModel->save([
'product_id' => $this->request->getPost('product_id'),
'imagefilepath' => $newName
]);
}
}
}
echo view('templates/admin-header',$data);
echo view('admin/products', $data);
echo view('templates/admin-sidebar');
echo view('templates/admin-footer');
}
else
{
echo view('templates/admin-header',$data);
echo view('admin/products', $data);
echo view('templates/admin-sidebar');
echo view('templates/admin-footer');
}
}
public function updateproduct(){
$model = new ProductModel();
$imageModel = new ProductImageModel();
$categoryModel = new CategoryModel();
$subcategoryModel = new SubcategoryModel();
$data = [
'title' => 'Products - Browse/Add/Update/Delete',
'products' => $model->paginate(10),
'categories' => $categoryModel->getCategory(),
'subcategories' => $subcategoryModel->getSubcategory(),
'pager' => $model->pager,
];
if ($this->request->getMethod() === 'post' && $this->validate([
'product_id' => 'required',
'product_name' => 'required',
'product_price' => 'required',
"count" => 'required'
]))
{
$product_id = $this->request->getPost('product_id');
$builder = $model->table('product');
$datas = [
'product_name' => $this->request->getPost('product_name'),
'product_price' => $this->request->getPost('product_price'),
'count' => $this->request->getPost('count')
];
$builder->set($datas);
$builder->where('product_id', $product_id);
$builder->update();
echo view('templates/admin-header',$data);
echo view('admin/products', $data);
echo view('templates/admin-sidebar');
echo view('templates/admin-footer');
}
else
{
echo view('templates/admin-header',$data);
echo view('admin/products', $data);
echo view('templates/admin-sidebar');
echo view('templates/admin-footer');
}
}
function delete(){
$product_id = $_POST['product_id'];
$model = new ProductModel();
$model ->where('product_id',$product_id)->delete();
echo 1;
}
}
The Products.php controller contains different functions to browse, add, update and delete products from mysql database.
Useful links, One time temporary link for email verification
4. Design the ProductModel
ProductModel.php
<?php namespace App\Models;
use CodeIgniter\Model;
class ProductModel extends Model{
protected $table = 'products';
protected $allowedFields = ['product_id','product_name','category_id','subcategory_id','product_price','count'];
}
5. Design the Product Image Model
ProductImageModel.php
<?php
namespace App\Models;
use CodeIgniter\Model;
class ProductImageModel extends Model{
protected $table = 'product_images';
protected $allowedFields = ['product_id','imagefilepath'];
public function getImage(){
return $this->findAll();
}
}
6. Design the Category Model
CategoryModel.php
<?php
use CodeIgniter\Model;
class CategoryModel extends Model{
protected $table = 'category';
protected $allowedFields = ['category_id','category_name'];
public function getCategory(){
return $this->findAll();
}
}
7. Design the subcategory Model
SubcategoryModel.php
<?php
namespace App\Models;
use CodeIgniter\Model;
class SubcategoryModel extends Model{
protected $table = 'subcategory';
protected $allowedFields = ['category_id','subcategory_id','subcategory_name'];
public function getSubcategory(){
return $this->findAll();
}
}
8. Design the View page products.php
products.php
<div class="body1 pt-5" id="bdy1">
<div class="ps-5 w-100 mx-auto pt-5">
<span class="text-danger">
<?= \Config\Services::validation()->listErrors() ?>
</span>
<i class="fas fw fa-plus text-primary" data-bs-toggle="modal" data-bs-target="#exampleModal"><span> Add New Product</span></i>
<!-- Modal -->
<span class="success text-success">
</span>
<span class="error text-danger">
</span>
<div class="modal fade" id="exampleModal" tabindex="-1" aria-labelledby="exampleModalLabel" aria-hidden="true">
<div class="modal-dialog">
<div class="modal-content">
<div class="modal-header">
<h5 class="modal-title" id="exampleModalLabel">Add Product</h5>
<button type="button" class="btn-close" data-bs-dismiss="modal" aria-label="Close"></button>
</div>
<div class="modal-body">
<form class="form-control" action="/admin/products" enctype="multipart/form-data" method="post">
<label for="product_id">Product Id:</label>
<input type="text" name="product_id" class="form-control" placeholder="Enter product id"> <br>
<label for="product_name">Product Name:</label>
<input type="text" name="product_name" class="form-control" placeholder="Enter product name"> <br>
<label for="product_price">Product Price:</label>
<input type="text" name="product_price" class="form-control" placeholder="Enter product price"> <br>
<label for="count">Count:</label>
<input type="text" name="count" class="form-control" placeholder="Enter number of product"> <br>
<label for="category">Choose Category</label>
<select name="category" id="category" onchange="myFunction11()" class="form-control sub">
<option selected>Choose category</option>
<?php foreach ($categories as $category): ?>
<option value="<?= esc($category['category_id']) ?>"><?= esc($category['category_name']) ?> </option>
<?php endforeach; ?>
</select><br>
<label for="subcategory">Choose Subcategory</label>
<select name="subcategory" id="subcategory" class="form-control subcat">
<option selected>select subcategory </option>
</select><br>
<input type="file" class="form-control" name="images[]" multiple /> <br>
<button type="submit" class="btn btn-primary">Add Product</button>
</form>
<script>
function myFunction11(){
var x = $('.sub').val();
var input = {
'category_id' : x
};
$.ajax({
url : '<?php echo site_url('/admin/selectsubcategory')?>',
type : 'POST',
data : input,
dataType: 'json',
success : function(response){
$(".subcat").html(response);
},
error: function(e){
alert(e);
}
});
}
</script>
</div>
<div class="modal-footer">
<button type="button" class="btn btn-secondary" data-bs-dismiss="modal">Close</button>
</div>
</div>
</div>
</div>
<div class="modal fade" id="exampleModal1" tabindex="-1" aria-labelledby="exampleModalLabel" aria-hidden="true">
<div class="modal-dialog">
<div class="modal-content">
<div class="modal-header">
<h5 class="modal-title" id="exampleModalLabel">Update Product</h5>
<button type="button" class="btn-close" data-bs-dismiss="modal" aria-label="Close"></button>
</div>
<div class="modal-body">
<form class="form-control" action="/admin/updates" method="post">
<label for="product_id">Product Id:</label>
<input type="text" id ="prd-id" readonly name="product_id" class="form-control" value=""> <br>
<label for="product_name">Product Name:</label>
<input type="text" id ="prd-name" name="product_name" class="form-control" value=""> <br>
<label for="category">Choose Category</label>
<select name="category" onchange="myFunction111()" class="form-control sub1">
<option selected>Choose category</option>
<?php foreach ($categories as $category): ?>
<option value="<?= esc($category['category_id']) ?>"><?= esc($category['category_name']) ?> </option>
<?php endforeach; ?>
</select><br>
<label for="subcategory">Choose Subcategory</label>
<select name="subcategory" class="form-control subcat1">
<option selected>select subcategory </option>
</select><br>
<label for="product_price">Product Price:</label>
<input type="text" id ="prd-price" name="product_price" class="form-control" value=""> <br>
<label for="count">Count:</label>
<input type="text" id ="prd-count" name="count" class="form-control" value=""> <br>
<button type="submit" class="btn btn-primary">Update Product</button>
</form>
</div>
<div class="modal-footer">
<button type="button" class="btn btn-secondary" data-bs-dismiss="modal">Close</button>
</div>
</div>
</div>
</div>
<script>
function myFunction111(){
var x = $('.sub1').val();
var input = {
'category_id' : x
};
$.ajax({
url : '<?php echo site_url('/admin/selectsubcategory')?>',
type : 'POST',
data : input,
dataType: 'json',
success : function(response){
$(".subcat1").html(response);
},
error: function(e){
alert(e);
}
});
}
</script>
<div class="pt-3">
<?php if (! empty($products) && is_array($products)) : ?>
<div class="table table-responsive">
<table class="table table-striped w-75 ">
<thead>
<tr>
<th class="text-left">Product Name</th>
<th class="text-left">Product Id</th>
<th class="text-left">Product Price</th>
<th class="text-left">Category</th>
<th class="text-left">Subcategory</th>
<th class="text-left">Product Count</th>
<th class="text-center">Actions</th>
</tr>
</thead>
<tbody>
<?php foreach ($products as $product): ?>
<tr>
<td><?= esc($product['product_name']) ?></td>
<td><?= esc($product['product_id']) ?></td>
<td><?= esc($product['product_price']) ?></td>
<td><?= esc($product['category_id']) ?></td>
<td><?= esc($product['subcategory_id']) ?></td>
<td><?= esc($product['count']) ?></td>
<td><button type="button" class="btn btn-outline-danger btn-sm dprd" data-id="<?= esc($product['product_id']) ?>" >Delete</button>
<button class="btn btn-outline-primary btn-sm uprd"
data-price-id="<?= esc($product['product_price']) ?>"
data-count-id="<?= esc($product['count']) ?>"
data-name-id="<?= esc($product['product_name']) ?>"
data-id="<?= esc($product['product_id']) ?>"
data-bs-toggle="modal" data-bs-target="#exampleModal1">Update</button>
</td>
</tr>
<?php endforeach; ?>
</tbody>
</table>
</div>
<script>
$(".dprd").click(function(){
var x = $(this).attr("data-id");
var el = this;
var input = {
"product_id" : x
};
var confirmalert = confirm("Are you sure?");
if (confirmalert == true){
$.ajax({
url : '<?php echo site_url('/admin/delete')?>',
type : 'POST',
dataType: 'json',
data : input,
success : function(response){
$(el).closest('tr').css('background','tomato');
$(el).closest('tr').fadeOut(800,function(){
$(this).remove();
});
},
error : function(){
$('.error').html("Error").show();
}
});
}
});
$(".uprd").click(function(){
var x1 = $(this).attr("data-id");
var x2 = $(this).attr("data-name-id");
var x3 = $(this).attr("data-price-id");
var x4 = $(this).attr("data-count-id");
$("#prd-id").val(x1);
$("#prd-name").val(x2);
$("#prd-price").val(x3);
$("#prd-count").val(x4);
});
</script>
<!-- Pagination -->
<div class="container w-50 mx-auto">
<?php if ($pager) :?>
<?php $pagi_path='admin/products'; ?>
<?php $pager->setPath($pagi_path); ?>
<?= $pager->links() ?>
<?php endif ?>
</div>
<?php else : ?>
<h3>No products</h3>
<?php endif ?>
</div>
</div>
</div>
</div>
View controls how application is presented to the users. Here user can browse all the products where the products can be updated or deleted according to the need. There is also an add produsct option where user can add products to the mysql database.
Here we have used ajax to make connection with controller function. The function performs the operation and returns the result to the view where we update the view without reloading the page.
9. Create Routes
ProductModel.php
$routes->get('/', 'Home::index');
$routes->get('/admin/products', 'Products::index',['filter' => 'auth']);
$routes->match(['get', 'post'], '/admin/products', 'Products::addproduct');
$routes->match(['get', 'post'],'/admin/delete', 'Products::delete');
$routes->match(['get', 'post'], '/admin/updates', 'Products::updateproduct');
Published on Oct 30, 2021
