Hi Dev,
In this blog, I will explain you how to delete multiple selected records useing datatables in php. We will show delete multiple selected records in php. you can easy delete multiple selected records useing datatables in php. we will show datatables delete multiple selected rows example in php.
I will give fulll example of php delete multiple selected records in datatables.
Step 1: Cretae TableIn this step, i will create table in mysql database.
CREATE TABLE `employee` ( `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT, `emp_name` varchar(80) NOT NULL, `salary` varchar(20) NOT NULL, `gender` varchar(10) NOT NULL, `city` varchar(80) NOT NULL, `email` varchar(80) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8;Step 1:Configuration
In this step, I will create php to mysql datatables configuration.
config.php
<?php
$host = "localhost"; /* Host name */
$user = "root"; /* User */
$password = "root"; /* Password */
$dbname = "php_tutorial"; /* Database name */
$con = mysqli_connect($host, $user, $password,$dbname);
// Check connection
if (!$con) {
die("Connection failed: " . mysqli_connect_error());
}
Step 2: HTML
In this step,Create a <table id='empTable' class='display dataTable' >. Add header row.
In the last cell add check all checkbox and a button for deleting checked records.
<!doctype html>
<html>
<head>
<title>Delete multiple selected records in DataTables – PHP</title>
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<!-- Datatable CSS -->
<link href='//cdn.datatables.net/1.10.19/css/jquery.dataTables.min.css' rel='stylesheet' type='text/css'>
<!-- jQuery Library -->
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<!-- Datatable JS -->
<script src="//cdn.datatables.net/1.10.19/js/jquery.dataTables.min.js"></script>
<!-- Bootstrap Cs-->
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/5.0.0-alpha1/css/bootstrap.min.css">
</head>
<body class="bg-dark">
<div class="container">
<div class="row">
<div class="col-md-10 offset-md-1">
<div class="card mt-1">
<div class="card-header">
<h5>PHP Delete multiple selected records in DataTables – nicesnippets.com</h5>
</div>
<div class="card-body">
<!-- Table -->
<table id='empTable' class='display dataTable table table table-hover'>
<thead>
<tr>
<th>Employee name</th>
<th>Email</th>
<th>Gender</th>
<th>Salary</th>
<th>City</th>
<th>Check All <input type="checkbox" class='checkall' id='checkall'><input type="button" id='delete_record' class="btn btn-sm ml-3 btn-danger" value='Delete' ></th>
</tr>
</thead>
</table>
</div>
</div>
</div>
</div>
</div>
</body>
</html>
Step 3: Script
->DataTable Initialization –
Initialize DataTable on <table id='empTable'>.
Add 'processing': true, 'serverSide': true, 'serverMethod': 'post'. Specify AJAX url, and data using 'ajax' option.
Using 'columns' option to specify field names which need to be read from AJAX response.
Remove sorting from the last column using columnDefs option.
->Check All –
If checkall checkbox is clicked then check it is checked or not. If checked then set all checkboxes checked by class='delete_check'.
If not checked then remove checked from all checkboxes.
>->Checkbox checked –
Create a checkcheckbox() function.
Count total checkboxes and checked checkboxes with class='delete_check' on the page.
If total checkboxes equal to total checked checkboxes then check the checkall checkbox otherwise uncheck the checkbox.
->Delete button –
Read all checked checkboxes by class='delete_check' and push value in deleteids_arr Array.
If deleteids_arr Array length is greater then display confirm alert. Send AJAX POST request to ‘ajaxfile.php’ for deleting
records when ok button gets clicked.Pass request: 2, deleteids_arr: deleteids_arr as data.
On successful callback reload the Datatable by calling
dataTable.ajax.reload().
var dataTable;
$(document).ready(function(){
// Initialize datatable
dataTable = $('#empTable').DataTable({
'processing': true,
'serverSide': true,
'serverMethod': 'post',
'ajax': {
'url':'ajaxfile.php',
'data': function(data){
data.request = 1;
}
},
'columns': [
{ data: 'emp_name' },
{ data: 'email' },
{ data: 'gender' },
{ data: 'salary' },
{ data: 'city' },
{ data: 'action' },
],
'columnDefs': [ {
'targets': [5], // column index (start from 0)
'orderable': false, // set orderable false for selected columns
}]
});
// Check all
$('#checkall').click(function(){
if($(this).is(':checked')){
$('.delete_check').prop('checked', true);
}else{
$('.delete_check').prop('checked', false);
}
});
// Delete record
$('#delete_record').click(function(){
var deleteids_arr = [];
// Read all checked checkboxes
$("input:checkbox[class=delete_check]:checked").each(function () {
deleteids_arr.push($(this).val());
});
// Check checkbox checked or not
if(deleteids_arr.length > 0){
// Confirm alert
var confirmdelete = confirm("Do you really want to Delete records?");
if (confirmdelete == true) {
$.ajax({
url: 'ajaxfile.php',
type: 'post',
data: {request: 2,deleteids_arr: deleteids_arr},
success: function(response){
dataTable.ajax.reload();
}
});
}
}
});
});
// Checkbox checked
function checkcheckbox(){
// Total checkboxes
var length = $('.delete_check').length;
// Total checked checkboxes
var totalchecked = 0;
$('.delete_check').each(function(){
if($(this).is(':checked')){
totalchecked+=1;
}
});
// Checked unchecked checkbox
if(totalchecked == length){
$("#checkall").prop('checked', true);
}else{
$('#checkall').prop('checked', false);
}
}
step 4: PHP
In this step, I will create ajaxfile.php file
->AJAX requests –
If $request == 1 then read DataTables POST values. If $searchQuery is not empty then prepare search query. Count total records without and with filter.
Fetch records from employee table where specify search query, order by and limit.
Loop on the fetched records and initialize $data Array with the keys specified in the columns option while dataTable initializing.
Assign checkbox in ‘action’ key. In the checkbox added onclick=‘checkcheckbox()’, pass $row[‘id’] in value attribute and class=‘delete_check’.
Assign required keys with values in $response Array and return in JSON format.
If $request == 2 then loop on the $deleteids_arr Array and execute DELETE query on the id.
<?php
include 'config.php';
$request = $_POST['request'];
// Datatable data
if($request == 1){
## Read value
$draw = $_POST['draw'];
$row = $_POST['start'];
$rowperpage = $_POST['length']; // Rows display per page
$columnIndex = $_POST['order'][0]['column']; // Column index
$columnName = $_POST['columns'][$columnIndex]['data']; // Column name
$columnSortOrder = $_POST['order'][0]['dir']; // asc or desc
$searchValue = $_POST['search']['value']; // Search value
## Search
$searchQuery = " ";
if($searchValue != ''){
$searchQuery .= " and (emp_name like '%".$searchValue."%' or
email like '%".$searchValue."%' or
city like'%".$searchValue."%' ) ";
}
## Total number of records without filtering
$sel = mysqli_query($con,"select count(*) as allcount from employee");
$records = mysqli_fetch_assoc($sel);
$totalRecords = $records['allcount'];
## Total number of records with filtering
$sel = mysqli_query($con,"select count(*) as allcount from employee WHERE 1 ".$searchQuery);
$records = mysqli_fetch_assoc($sel);
$totalRecordwithFilter = $records['allcount'];
## Fetch records
$empQuery = "select * from employee WHERE 1 ".$searchQuery." order by ".$columnName." ".$columnSortOrder." limit ".$row.",".$rowperpage;
$empRecords = mysqli_query($con, $empQuery);
$data = array();
while ($row = mysqli_fetch_assoc($empRecords)) {
$data[] = array(
"emp_name"=>$row['emp_name'],
"email"=>$row['email'],
"gender"=>$row['gender'],
"salary"=>$row['salary'],
"city"=>$row['city'],
"action"=>"<input type='checkbox' class='delete_check' id='delcheck_".$row['id']."' onclick='checkcheckbox();' value='".$row['id']."'>"
);
}
## Response
$response = array(
"draw" => intval($draw),
"iTotalRecords" => $totalRecords,
"iTotalDisplayRecords" => $totalRecordwithFilter,
"aaData" => $data
);
echo json_encode($response);
exit;
}
// Delete record
if($request == 2){
$deleteids_arr = $_POST['deleteids_arr'];
foreach($deleteids_arr as $deleteid){
mysqli_query($con,"DELETE FROM employee WHERE id=".$deleteid);
}
echo 1;
exit;
}
It will help you...
