Delete Multiple Rows with Checkbox using jQuery, PHP & MySQL

Deleting records one by one is very time consuming when there are too many records. We can make it more user friendly by allowing users to delete multiple records on a single click. You just need to add checkbox with each records and allow to select checkbox and then implement functionality to delete multiple selected records through single delete button.

In this tutorial, you will learn how to implement multiple selected records delete with checkbox using jQuery, PHP and MySQL. We will cover this tutorial in easy steps with live demo.

Also, read:

• Load Dynamic Content in Bootstrap Popover with Ajax, PHP & MySQL
• Create Dynamic Bootstrap Tabs with PHP & MySQL
• Bootstrap Contact Form with Captcha using Ajax and PHP
• Multi Select Dropdown with Checkbox using Bootstrap, jQuery and PHP

So let’s start the coding. Before begin, take a look on files structure used for this tutorial.

• Index.php
• delete.action.php
• delete_script.js

Step1: Create Database Table
First we will create MySQL database table employee to display employee records.

CREATE TABLE IF NOT EXISTS `employee` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'primary key',
  `employee_name` varchar(255) NOT NULL COMMENT 'employee name',
  `employee_salary` double NOT NULL COMMENT 'employee salary',
  `employee_age` int(11) NOT NULL COMMENT 'employee age',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

Then import eployee records to employee table using below insert statement.

INSERT INTO `employee` (`id`, `employee_name`, `employee_salary`, `employee_age`) VALUES
(1, 'Tiger Nixon', 3208000, 61),
(2, 'Garrett Winters', 170750, 63),
(3, 'Ashton Cox', 86000, 66),
(4, 'Cedric Kelly', 433060, 22),
(5, 'Airi Satou', 162700, 33),
(6, 'Brielle Williamsons', 372000, 61),
(7, 'Herrod Chandler', 137500, 59),
(8, 'Rhona Davidson', 327900, 55),
(9, 'Colleen Hurst', 205500, 39),
(10, 'Sonya Frost', 103600, 23);

Step2: Create Database Connection
We will create db_connect.php file to make connection with MySQL database to display employee records.

<?php
/* Database connection start */
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "demos";
$conn = mysqli_connect($servername, $username, $password, $dbname);
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}
?>

Steps3: Include Bootstrap and JavaScript Files
As in this tutorial we have created HTML using Bootstrap, so we include Bootstrap files and also jQuery in head tag in index.php.

<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/css/bootstrap.min.css">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/css/bootstrap-theme.min.css">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.3/jquery.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/js/bootstrap.min.js"></script>
<script type="text/javascript" src="script/delete_script.js"></script>	

Steps4: Display Employee Records with Checkbox and Delete Button

Now in index.php, we will display employee records from MySQL database table employee with checkbox and delete button.

<div class="container">
	<h2>Example: Delete Multiple Rows with Checkbox using jQuery, PHP & MySQL</h2>		
	<table id="employee_grid" class="table table-condensed table-hover table-striped bootgrid-table" width="60%" cellspacing="0">
		<thead>
		  <tr>
			 <th><input type="checkbox" id="select_all"></th>
			 <th>Name</th>
			 <th>Salary</th>
			 <th>Age</th>			 
		  </tr>
	    </thead>
		<tbody>
		<?php
		$sql = "SELECT id, employee_name, employee_salary, employee_age FROM employee LIMIT 5";
		$resultset = mysqli_query($conn, $sql) or die("database error:". mysqli_error($conn));
		while( $rows = mysqli_fetch_assoc($resultset) ) { 
		?>
		  <tr id="<?php echo $rows["id"]; ?>">
			 <td><input type="checkbox" class="emp_checkbox" data-emp-id="<?php echo $rows["id"]; ?>"></td>
			 <td><?php echo $rows["employee_name"]; ?></td>
			 <td><?php echo $rows["employee_salary"]; ?></td>
			 <td><?php echo $rows["employee_age"]; ?></td>			 
		  </tr>
		<?php
		}
		?>
		</tbody>  
	</table>		
	<div class="row">
		<div class="col-md-2 well">
			<span class="rows_selected" id="select_count">0 Selected</span>
			<a type="button" id="delete_records" class="btn btn-primary pull-right">Delete</a>
		</div>
	</div>	
</div>	

Steps5: Select All Checkbox using jQuery
Now in delete_script.js, we will handle functionality to select all checkbox next to each employee records when click on “Select All” to make select all checkboxes. Also handle to display selected rows count.

$(document).on('click', '#select_all', function() {          	
		$(".emp_checkbox").prop("checked", this.checked);
		$("#select_count").html($("input.emp_checkbox:checked").length+" Selected");
	});	
	$(document).on('click', '.emp_checkbox', function() {		
		if ($('.emp_checkbox:checked').length == $('.emp_checkbox').length) {
			$('#select_all').prop('checked', true);
		} else {
			$('#select_all').prop('checked', false);
		}
		$("#select_count").html($("input.emp_checkbox:checked").length+" Selected");
	});  

Steps6: Delete All Selected Records with jQuery Ajax
Now we will handle functionality to delete all selected records using jQuery Ajax. We will get selected checkbox details and then make Ajax request to delete_action.php script to delete records from MySQL database table employee.

// delete selected records
$('#delete_records').on('click', function(e) { 
	var employee = [];  
	$(".emp_checkbox:checked").each(function() {  
		employee.push($(this).data('emp-id'));
	});	
	if(employee.length <=0)  {  
		alert("Please select records.");  
	}  
	else { 	
		WRN_PROFILE_DELETE = "Are you sure you want to delete "+(employee.length>1?"these":"this")+" row?";  
		var checked = confirm(WRN_PROFILE_DELETE);  
		if(checked == true) {			
			var selected_values = employee.join(","); 
			$.ajax({ 
				type: "POST",  
				url: "delete_action.php",  
				cache:false,  
				data: 'emp_id='+selected_values,  
				success: function(response) {	
					// remove deleted employee rows
					var emp_ids = response.split(",");
					for (var i=0; i < emp_ids.length; i++ ) {						
						$("#"+emp_ids[i]).remove();
					}									
				}   
			});				
		}  
	}  
});

Steps7: Delete Records from MySQL Database
Now finally in delete_action.php, we will handle functionality to get deleted employee ids and then execute MySQL delete query to delete employee records from database.

<?php
include_once("db_connect.php");
if(isset($_POST['emp_id'])) {
	$emp_id = trim($_POST['emp_id']);	
	$sql = "DELETE FROM employee WHERE id in ($emp_id)"
	$resultset = mysqli_query($conn, $sql) or die("database error:". mysqli_error($conn));
	echo $emp_id;
}
?>

You may also like:

• Build Live Chat System with Ajax, PHP & MySQL
• Create Event Calendar with jQuery, PHP and MySQL
• Build Invoice System with PHP & MySQL
• Push Notification System with PHP & MySQL
• Create Bootstrap Cards with PHP and MySQL
• Build Content Management System with PHP & MySQL
• Convert Unix Timestamp To Readable Date Time in PHP
• Ajax Drop Down Selection Data Load with PHP & MySQL
• Inventory Management System with Ajax, PHP & MySQL
• Drag and Drop File Upload using jQuery and PHP
• Load Dynamic Content in Bootstrap Popover with Ajax, PHP & MySQL

You can view the live demo from the Demo link and can download the script from the Download link below.
Demo Download