Introduction

In this tutorial we will see how to delete multiple rows from table using PHP, MySQL, AJAX, jQuery. We are going to delete rows from HTML as well as MySQL database table. We will use IN clause with WHERE condition in MySQL query to delete multiple rows from database 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

PHP 7.4.3, Apache HTTP Server 2.4, MySQL 8.0.17, jQuery 3.5.0

MySQL Table

As we are going to deleet data from MySQL database, so we need a table for it. So we will create a table called product under roytuts database.

We will also dump some data to test our application.

CREATE TABLE IF NOT EXISTS `product` (
  `id` int(10) 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=24 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO `product` (`id`, `name`, `code`, `price`) VALUES
	(1, 'American Tourist', 'AMTR01', 12000),
	(2, 'EXP Portable Hard Drive', 'USB02', 5000),
	(3, 'Shoes', 'SH03', 1000),
	(4, 'XP 1155 Intel Core Laptop', 'LPN4', 80000),
	(5, 'FinePix Pro2 3D Camera', '3DCAM01', 150000),
	(6, 'Simple Mobile', 'MB06', 3000),
	(7, 'Luxury Ultra thin Wrist Watch', 'WristWear03', 3000),
	(8, 'Headphone', 'HD08', 400),
	(9, 'Test 1', 'test1', 10),
	(10, 'Test 2', 'test2', 11),
	(11, 'Test 3', 'test3', 12);

Create Project

It’s assumed that you have setup Apache, PHP and MySQL in Windows system.

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

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.

Database Configurations

Create db.php file for various database operations, such as, selecting data from MySQL database, deleting data from MySQL database, etc.

Please do not forget to change the database credentials as per your database.

<?php

/**
* Author : https://roytuts.com
*/
	
$dbConn = mysqli_connect('localhost', 'root', 'root', 'roytuts') or die('MySQL connect failed. ' . mysqli_connect_error());

function dbQuery($sql) {
	global $dbConn;
	$result = mysqli_query($dbConn, $sql) or die(mysqli_error($dbConn));
	return $result;
}

function dbFetchAssoc($result) {
	return mysqli_fetch_assoc($result);
}

function closeConn() {
	global $dbConn;
	mysqli_close($dbConn);
}
	
//End of file

Table Rows Deletion

We will delete the selected rows from database table using IN clause with WHERE condition in MySQL query.

We will create REST API in PHP, which will be invoked from AJAX URL.

The below REST API allows any origin to be accessible for http method POST only. JSON parameter will be used in the request body for deleting rows.

We create a PHP file called delete.php with the following code:

<?php

/**
* Author : https://roytuts.com
*/

require_once 'db.php';

header("Access-Control-Allow-Origin: *");
header("Access-Control-Allow-Methods: POST");

if ($_SERVER['REQUEST_METHOD'] === 'POST') {
	// get posted data
	$data = json_decode(file_get_contents("php://input", true));
	
	$sql = "DELETE FROM product p WHERE p.id IN (" . mysqli_real_escape_string($dbConn, $data->ids) . ")";
	
	$result = dbQuery($sql);
	
	if($result) {
		echo '<span style="color:green;">Product(s) successfully deleted</span>';
	} else {
		echo '<span style="color:red;">Something went wrong during product deletion</span>';
	}
}

//End of file

In PHP we retrieve the JSON body parameter using the following code.

file_get_contents("php://input", true)

Fetch and Display Products Details

Next we will fetch rows from database table and display them on a HTML table.

We have put checkbox at each row to select the row for deletion. On the other hand we have also put the checkbox on table head to select all rows for deletion.

We have applied basic style and we are using AJAX request to perform deletion activities.

Write below code into index.php file.

<!DOCTYPE html>
<html>
<head>
	<meta charset="utf-8">
	<title>Multiple Table Rows Deletion Example in PHP, AJAX, jQuery, MySQL</title>
	<link rel="stylesheet" type="text/css" href="assets/css/table.css"/>
	<script src="https://code.jquery.com/jquery-3.5.0.min.js"></script>
	<script src="assets/js/app.js"></script>
</head>
<body>

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

	<div id="body">
		<?php
			require_once 'db.php';
			
			$sql = "SELECT * FROM product";
			$results = dbQuery($sql);

			if($results) {
		?>
			<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;
						while($p = dbFetchAssoc($results)) {
							$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>

Table Style

We apply color on rows according to odd or even row number. We apply black color on table head.

Put the below code into assets/css/table.css file.

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

REST API Call

Now we use AJAX with jQuery to call the REST API for deleting rows. We also use jQuery for selecting rows and getting the checkbox values.

$(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/php-ajax-jquery-multiple-rows-delete/delete.php",
				data: JSON.stringify({'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>');
		}
	});
});

Testing the Application

Make sure your Apache HTTP Server, MySQL server are up and running fine. You need to create and dump data into table product under roytuts database in MySQL server.

The below youtube video shows how to test the application and what is the expected output.

Source Code

Download Source Code

Thanks for reading.