Skip to main content

Delete Multiple Rows with Checkbox using jQuery, PHP & MySQL

Deleting records one by one is very time consuming when there are too many records. You can make it more user friendly by allowing users to delete multiple records on a single click. You can add checkbox with each records and allow to select checkbox and then implement functionality to delete multiple selected records through single delete button. So in this tutorial you will learn how to implement multiple records delete with checkbox using jQuery, PHP and MySQL.

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.

// select all checkbox
$('#select_all').on('click', function(e) {
if($(this).is(':checked',true)) {
$(".emp_checkbox").prop('checked', true);
}
else {
$(".emp_checkbox").prop('checked',false);
}
// set all checked checkbox count
$("#select_count").html($("input.emp_checkbox:checked").length+" Selected");
});
// set particular checked checkbox count
$(".emp_checkbox").on('click', function(e) {
$("#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 can view the live demo from the Demo link and can download the script from the Download link below.
Demo

Leave a Reply

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

Shares

Subscribe For Latest Updates

Signup for our newsletter and get notified when we publish new articles for free!