Multiple Rows Deletion
Here I am going to build an example on how to select multiple rows from HTML table and delete these multiple selected table rows from MySQL database using CodeIgniter 4 with the help of AJAX technique in jQuery.
In this tutorial I am going to fetch all records from the MySQL database and displaying on an HTML table. I will put a checkbox against each row in the HTML table. So, a user will be able to select a particular row for deletion. Another checkbox will be put on the header of the HTML table and selecting this checkbox will select all checkboxes of the HTML table.
Related Posts:
I am using jQuery framework to check or uncheck checkbox(es) on the HTML table. If user selects manually all checkboxes for rows on the table then the checkbox in table header will be checked automatically to show that all checkboxes on the table body are checked. If any of the checkboxes on the table body gets unchecked the header checkbox will be unchecked automatically.
Prerequisites
Codeigniter 4.1.4, PHP 7.4.23, MySQL 8.0.26, jQuery 3.6.0
Project Directory
It’s assumed that you have setup PHP and Codeigniter 4.1.4 in Windows system.
Now I will create a project root directory called codeigniter-ajax-delete-multiple-rows anywhere in the system.
Now move all the directories and files from CodeIgniter framework into codeigniter-ajax-delete-multiple-rows 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
Create a table called product in the MySQL database using the following 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 don’t want to create any new product through our application, so I will dump a product details using SQL statement.
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 bulk delete operations on the product table. The following source code is written into app/Models/ProductModel.php file.
So, I will perform delete operations using delete() function specifying the conditions using whereIn() function in the model class.
To delete a single record from the database, you can use where() function to pass the condition.
<?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_products_by_ids($ids) {
if($this->db->table($this->product)->whereIn('id', $ids)->delete()) {
return true;
} else {
return false;
}
}
}The function delete_products_by_ids($ids) accepts multiple product ids in the form of an array and whereIn() function is used to delete multiple products from the database. wherein() basically converts to where in() condition in SQL.
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.
<?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 delete_products() {
if ('post' === $this->request->getMethod() && $this->request->getPost('ids')) {
$ids = explode(',', $this->request->getPost('ids'));
$model = new ProductModel();
$results = $model->delete_products_by_ids($ids);
if ($results === true) {
echo '<span style="color:green;">Product(s) successfully deleted</span>';
} else {
echo '<span style="color:red;">Something went wrong during product deletion</span>';
}
} else {
echo '<span style="color:red;">You must select at least one product row for deletion</span>';
}
}
}In the delete_products() function, I have checked whether the request HTTP method is POST or not and accordingly perform the deletion operation.
The comma separated product ids passed using AJAX technique with the help of jQuery from the client side are converted into an array of ids using PHP’s explode() function.
Finally, model’s function is called to delete the selected products.
View File
The view file is responsible for displaying information on the web page.
So, I will create a view files products.php under app/Views folder.
I have included jQuery from CDN link and the required jQuery code from public/delete.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 have displayed all rows in the table body from the database and a table header. In header I have put one checkbox to check all checkboxes in the table body. I have also created individual checkbox to select the individual row on table body.
I have put name for each checkbox and value with product id. I have placed a button above the table for deleting the selected products from the table.
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Multiple Table Rows Deletion in Codeigniter 4, AJAX, jQuery, MySQL</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.0.min.js" crossorigin="anonymous"></script>
<script src="delete.js"></script>
</head>
<body>
<div>
<h1>Multiple Table Rows Deletion in Codeigniter 4, AJAX, jQuery, MySQL</h1>
<div id="body">
<?php
if ($product_list) {
?>
<div id="msg"></div>
<button id="delete_selected">Delete Selected Product(s)</button>
<table class="datatable">
<thead>
<tr>
<th><input id="check_all" type="checkbox"></th>
<th>ID</th>
<th>Code</th>
<th>Name</th>
<th>Price</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><input type="checkbox" name="row-check" value="<?php echo $p->id;?>"></td>
<td><?php echo $p->id; ?></td>
<td><?php echo $p->code; ?></td>
<td><?php echo $p->name; ?></td>
<td><?php echo $p->price; ?></td>
</tr>
<?php
}
?>
</tbody>
</table>
<?php
} else {
echo '<div style="color:red;"><p>No Record Found</p></div>';
}
?>
</div>
</div>
</body>
</html>Check/Uncheck/Deletion using jQuery
Now I will write jQuery code in public/delete.js file to check or uncheck the checkboxes.
If you click on the header checkbox then all checkboxes will be checked on the table body.
If you check all checkboxes manually, eventually the header checkbox will be checked automatically.
Once you click on the button for deleting selected products then an AJAX call will be performed to delete the selected products from the MySQL database.
$(function() {
//If check_all checked then check all table rows
$("#check_all").on("click", function () {
if ($("input:checkbox").prop("checked")) {
$("input:checkbox[name='row-check']").prop("checked", true);
} else {
$("input:checkbox[name='row-check']").prop("checked", false);
}
});
// Check each table row checkbox
$("input:checkbox[name='row-check']").on("change", function () {
var total_check_boxes = $("input:checkbox[name='row-check']").length;
var total_checked_boxes = $("input:checkbox[name='row-check']:checked").length;
// If all checked manually then check check_all checkbox
if (total_check_boxes === total_checked_boxes) {
$("#check_all").prop("checked", true);
}
else {
$("#check_all").prop("checked", false);
}
});
$("#delete_selected").on("click", function () {
var ids = '';
var comma = '';
$("input:checkbox[name='row-check']:checked").each(function() {
ids = ids + comma + this.value;
comma = ',';
});
//console.log(ids);
if(ids.length > 0) {
$.ajax({
type: "POST",
url: "http://localhost:8080/index.php/product/delete_products",
data: {'ids': ids},
dataType: "html",
cache: false,
success: function(msg) {
$("#msg").html(msg);
window.setTimeout(function(){
window.location.href = "http://localhost:8080/";
}, 5000);
},
error: function(jqXHR, textStatus, errorThrown) {
$("#msg").html("<span style='color:red;'>" + textStatus + " " + errorThrown + "</span>");
}
});
} else {
$("#msg").html('<span style="color:red;">You must select at least one product row for deletion</span>');
}
});
});Once products deleted from the database, you will see successful message but your page will not be refreshed due to an AJAX call. So, I have put the following code snippets that will automatically reload the page after 5 seconds.
window.setTimeout(function(){
window.location.href = "http://localhost:8080/";
}, 5000);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.
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.
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 serveYour application will be running on localhost and port 8080.
Testing Bulk Deletions
Now hit the URL http://localhost:8080 in the browser and you will find the below page:
If you do not select any row and try to delete, then you will see the following error message:
When you check the header checkbox then all checkboxes will automatically be checked:
Let’s delete products which are having ids 4 and 5:
Once deleted, you will see a success message:
Finally, your page will be reloaded after 5 seconds to reflect the changes.