Posted in AJAX, Codeigniter

How To Delete Multiple Rows From Table Using Codeigniter AJAX jQuery MySQL

Data Deletion

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

I am 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 – 7.4.27, MySQL 8.0.17 – 8.0.26, jQuery 3.5.0 – jQuery 3.6.0, Apache 2.4 (Optional)

Project Directory

It’s assumed that you have setup PHP and Codeigniter in Windows system.

Now I will create a project root directory called codeIgniter-ajax-jquery-multiple-delete.

Now move all the directories and files from CodeIgniter framework into codeIgniter-ajax-jquery-multiple-delete 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;

Dump Product Details

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);

Autoload Configuration

It is a good idea to create some configurations, such as, auto-loading for helpers to avoid loading every time I 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 I 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',
        ...
);

Model Class

I will 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, I will fetch all products from database table product. If no product is found then I simply return NULL.

I will also delete products for the given product ids, so multiple products will be deleted from MySQL database table.

<?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 */

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 */

View File

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

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

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

I have displayed all rows in the table body from the database and a table header. In the header I have put one checkbox to check all checkboxes in the table body. I have also 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 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="https://code.jquery.com/jquery-3.6.0.min.js" integrity="sha256-/xUj+3OJU5yExlq6GSYGSHk7tPXikynS7ogEvDej/m4=" crossorigin="anonymous"></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 I will write jQuery code in product.js file to check or uncheck the checkboxes.

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

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

Once I click on the button for deleting selected products then I 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",
                                //url: "http://localhost/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

I have applied some basic style on the HTML table. I make the header black. I apply 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;
}

Configuring Route

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

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

Testing Multiple Rows Deletion

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

Source Code

Download

Leave a Reply

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