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.
So let’s start the coding. Before begin, take a look on files structure used for this tutorial.
Step1: Create Database Table
First we will create MySQL database table employee to display employee records.
Then import eployee records to employee table using below insert statement.
Step2: Create Database Connection
We will create db_connect.php file to make connection with MySQL database to display employee records.
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.
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.
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.
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.
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.
You can view the live demo from the Demo link and can download the script from the Download link below.
Demo [sociallocker]Download[/sociallocker]
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.
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 can view the live demo from the Demo link and can download the script from the Download link below.
Demo [sociallocker]Download[/sociallocker]
Warning: include(): Failed opening 'container.php' for inclusion (include_path='C:\xampp\php\PEAR') in C:\Users\aslamswt\Dropbox\www\deletemultiplerows\index.php on line 8
ردحذفits showing error of missing Container.php, can you resolve the issue, please
Sorry! there are Container.php file missing in download zip. Now file updated, please download zip file again.
ردحذفHi, Im testing the script, but do not delete the records :(, I uncomment the SQL query but do not work..
ردحذفThank you for your help and for the script.
Its working fine in demo page, plz provide your code to me to check the cause of issue.
ردحذفhello sir its really nice script but its not deleting multiple records. it selects all but deletes only one record out of all selected records. can you please help me ?
ردحذفIts working fine at my end. please provide more details.
ردحذفإرسال تعليق