Delete Multiple Selected Records in DataTables - PHP

DataTables plugin is the easiest way to add pagination on the page with all the required features.

For adding delete control you can either add a delete button with each record or add checkboxes and a single delete button.

In this tutorial, I show how you add multiple checkboxes and delete selected records on a single click in DataTables using jQuery AJAX.

Delete Multiple Selected Records in DataTables - PHP

Download

Table structure
Configuration
Download & Include
HTML
Script
PHP
Output
Conclusion

1. Table structure

Create employee table.

CREATE TABLE `employee` (
  `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `emp_name` varchar(60) NOT NULL,
  `salary` varchar(50) NOT NULL,
  `gender` varchar(10) NOT NULL,
  `city` varchar(80) NOT NULL,
  `email` varchar(80) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2. Configuration

Create a config.php for database connection.

Completed Code

<?php

$host = "localhost"; /* Host name */
$user = "root"; /* User */
$password = ""; /* Password */
$dbname = "tutorial"; /* Database name */

$con = mysqli_connect($host, $user, $password,$dbname);
// Check connection
if (!$con) {
  die("Connection failed: " . mysqli_connect_error());
}

3. Download & Include

Download Datatables from here.
Include datatables.min.css and datatables.min.js in <head> section and also include jQuery Library.
You can also use CDN.

<!-- Datatable CSS -->
<link href='https://cdn.datatables.net/1.12.1/css/jquery.dataTables.min.css' rel='stylesheet' type='text/css'>

<!-- jQuery Library --> 
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.6.0/jquery.min.js"></script> 

<!-- Datatable JS -->
<script src="https://cdn.datatables.net/1.12.1/js/jquery.dataTables.min.js"></script>

4. HTML

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.

Completed Code

<div >

   <!-- Table -->
   <table id='empTable' class='display dataTable'>
     <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' value='Delete' ></th>
       </tr>
     </thead>

   </table>
</div>

5. 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().

Completed Code

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

6. PHP

Create an ajaxfile.php file.

From this file handle two 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.

Completed Code

<?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 = mysqli_real_escape_string($con,$_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 = array();

   if(isset($_POST['deleteids_arr'])){
      $deleteids_arr = $_POST['deleteids_arr'];
   }
   foreach($deleteids_arr as $deleteid){
      mysqli_query($con,"DELETE FROM employee WHERE id=".$deleteid);
   }

   echo 1;
   exit;
}

7. Output

View Output

8. Conclusion

Create a separate column for adding checkboxes with records.

On the delete button click loop on the checked checkboxes and send AJAX request to DELETE it and refresh the DataTable.

If you found this tutorial helpful then don't forget to share.

4 thoughts on “Delete Multiple Selected Records in DataTables – PHP”

Kailash Singh

May 28, 2019 at 1:33 pm

There is an error. If i checked serial number 2 and then i go on second pagination. then they uncheck my previous pagination check.
- Yogesh Singh
  
  May 28, 2019 at 4:50 pm
  
  Hi Kailash,
  This is not an error. Datatable each time load the content when navigating to another page this is why checked checkboxes are been unchecked.
  - gulshan
    
    June 22, 2021 at 5:01 am
    
    is there any solution check all row including another page and Postthe data in php?
  - Yogesh Singh
    
    June 24, 2021 at 2:59 pm
    
    Hi Gulshan,
    It can be done using COOKIES and SESSION but I didn’t test it yet.

Delete Multiple Selected Records in DataTables – PHP

Contents

1. Table structure

2. Configuration

3. Download & Include

4. HTML

5. Script

6. PHP

7. Output

8. Conclusion

4 thoughts on “Delete Multiple Selected Records in DataTables – PHP”

Leave a Comment Cancel reply