How to delete multiple rows from table using Codeigniter, AJAX, jQuery, MySQL

Introduction

In this tutorial we build an application using Codeigniter framework to fetch all the products from MySQL database and display on the HTML table. We will put checkbox against each row on the table so that user will be able to select a particular row for deletion. We will also put a checkbox on the table header and selecting this checkbox all rows on the table will be selected for deletion.

We are using jQuery to check or check the checkbox. 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 3.1.11, PHP 7.4.3, MySQL 8.0.17, jQuery 3.5.0

Create Project Directory

It’s assumed that you have setup Apache 2.4, PHP 7.4.3 and Codeigniter 3.1.11 in Windows system.

Now we will create a project root directory called codeIgniter-ajax-jquery-multiple-delete the Apache server’s htdocs folder.

Now move all the directories and files from CodeIgniter 3.1.11 framework into codeIgniter-ajax-jquery-multiple-delete directory.

We may not mention the project root directory in subsequent sections and we will assume that we are talking with respect to the project root directory.

Create MySQL table

Create a table called product in the MySQL database using the following structure.

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

Dump Product Details

We don’t want to create any new product through our application, so we 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);

Autoload Configuration

We need some configurations, such as, auto-loading for helpers to avoid loading every time we need to use.

Modify application/config/autoload.php file for auto-loading libraries and helper functions.

This one time auto-loading gives flexibility to uniformly use the helpers and libraries anywhere throughout the application without loading repeatedly.

$autoload['libraries'] = array('database');
$autoload['helper'] = array('url');

Database Configurations

Now we will configure database connection into application/config/database.php file. Please make sure to change according to your database settings.

$db['default'] = array(
	'dsn'	=> '',
	'hostname' => 'localhost',
	'username' => 'root',
	'password' => 'root',
	'database' => 'roytuts',
        ...
	'char_set' => 'utf8mb4',
	'dbcollat' => 'utf8mb4_unicode_ci',
        ...
);

Create Model Class

We perform database operations to fetch or delete product data into database.

Create a file product_model.php under applications/models folder to create the required model class.

In the below model class we fetch all products from database table product. If no product is found then we simply return NULL.

We also delete products for given product ids.

<?php

defined('BASEPATH') OR exit('No direct script access allowed');
	
/**
* Description of ProductModel
*
* @author https://roytuts.com
*/

class Product_Model extends CI_Model {
	
	private $product = 'product';

	function get_products() {		
		$query = $this->db->get($this->product);
		
		if($query->result()) {
			return $query->result();
		}
		
		return NULL;
	}
	
	function delete_products_by_ids($ids) {
		$this->db->where_in('id', $ids);
		
		if($this->db->delete($this->product)) {
			return TRUE;
		} else {
			return FALSE;
		}
	}
	
}

/* End of file product_model.php */
/* Location: ./application/models/product_model.php */

Create Controller Class

Create a controller file product.php under application/controllers with the following source code.

The below controller class handles request and response for clients.

The controller class has a method called index() that displays all products on the view or UI (User Interface).

The delete_products() function performs delete operation for the given product ids. At least one product must be selected from the HTML table on UI to perform delete operation. On successful or unsuccessful delete operation, a message will be displayed on the UI.

<?php
defined('BASEPATH') OR exit('No direct script access allowed');

/**
* Description of Product
*
* @author https://roytuts.com
*/

class Product extends CI_Controller {
	
	function __construct() {
        parent::__construct();
		$this->load->model('product_model', 'pm');
    }

	public function index()	{
		$data['products'] = $this->pm->get_products();
		$this->load->view('products', $data);
	}
	
	public function delete_products() {
		if (isset($_POST['ids'])) {
			$ids = explode(',', $_POST['ids']);
			
			$results = $this->pm->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 for deletion</span>';
		}
	}
	
}

/* End of file product.php */
/* Location: ./application/controllers/product.php */

Create View File

The view file is responsible for displaying information on the web page.

So we will create a view files products.php under application/views folder.

We have included jQuery from CDN link and the required jQuery code from product.js file.We have also included CSS file table.css to apply some basic style on HTML table.

We have displayed all rows in the table body from the database and a table header. In header we have put one checkbox to check all checkboxes in the table body. We have also individual checkbox to select the individual row on table body.

We have put name for each checkbox and value with product id.

We have placed a button for deleting the selected products from the table.

<?php
	defined('BASEPATH') OR exit('No direct script access allowed');
?>
<!DOCTYPE html>
<html lang="en">
<head>
	<meta charset="utf-8">
	<title>Multiple Table Rows Deletion Example in Codeigniter, AJAX, jQuery, MySQL</title>
	<link rel="stylesheet" type="text/css" href="<?=base_url()?>assets/css/table.css"/>
	<script src="https://code.jquery.com/jquery-3.5.0.min.js"></script>
	<script src="<?=base_url()?>assets/js/product.js"></script>
</head>
<body>

<div>
	<h1>Multiple Table Rows Deletion Example in Codeigniter, AJAX, jQuery, MySQL</h1>

	<div id="body">
		<?php
			if ($products) {
		?>
		<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 ($products 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 we will write jQuery code in product.js file to check or uncheck the checkboxes.

If we click on the header checkbox then we select all checkboxes on the table body.

If we check each checkbox manually and when we check all checkboxes eventually then we also check the header checkbox.

Once we click on the button for deleting selected products then we make an AJAX call to perform the delete operation for selected products.

$(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 = ',';			
		});		
		
		if(ids.length > 0) {
			$.ajax({
				type: "POST",
				url: "http://localhost:8000/codeIgniter-ajax-jquery-multiple-delete/index.php/product/delete_products",
				data: {'ids': ids},
				dataType: "html",
				cache: false,
				success: function(msg) {
					$("#msg").html(msg);
				},
				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 for deletion</span>');
		}
	});
});

Apply Style

We apply some basic style on the HTML table. We make the header black. We apply color on odd or even rows of the HTML table. We 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;
}

Configuring Route

We need to replace our default controller in application/config/routes.php file as shown below:

$route['default_controller'] = 'product';

Testing the Application

Now we will test the application. The following youtube video shows how it works.

Source Code

Download Source Code

Thanks for reading.

Leave a Comment