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


Contents

  1. Table structure
  2. Configuration
  3. Download & Include
  4. HTML
  5. Script
  6. PHP
  7. Output
  8. 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.