This post is 10th part of a series called Getting Started with Datatable 1.10 .

  1. DataTable demo (Server side) in Php,Mysql and Ajax
  2. jQuery Datatable with Custom Json format in Php, Mysql
  3. DataTable (Server side) Custom Column Search
  4. Datatable Scroller (Server Side)
  5. DataTable Responsive (Server side)
  6. Datatable Custom Loader
  7. Datatable Colvis (Column Visibility Customization)
  8. DataTable Table tools
  9. DataTable Custom Search By Tags Input (Server side)
  10. DataTable Search By Datepicker (Server side)
  11. Datatable Bulk Delete (Server Side)
  12. Datatable Page Resize (Server Side)

Two days ago one of the audience appreciated my datatable series on coderexample. He also requested a tutorial about bulk delete in datatable.

So I forked my first tutorial about datatable and modify as our needs. Before step into this tutorial, I will strongly recommend to check my first tutorial about datatable

datatable-bulk-delete-server-side

Here is our folder structure:

  • js
  • css
  • images
  • index.php
  • employee-grid-data.php
  • employee-delete.php

First I have modified the table markup in index.php. Add a checkbox in first column in table header, which will be a switch to select or deselect all rows. Also add a delete button to trigger delete operation.

01
02
03
04
05
06
07
08
09
10
<table id="employee-grid"  cellpadding="0" cellspacing="0" border="0" class="display" width="100%">
        <thead>
            <tr>
                <th><input type="checkbox"  id="bulkDelete"  /> <button id="deleteTriger">Delete</button></th>
                <th>Employee name</th>
                <th>Salary</th>
                <th>Age</th>
            </tr>
        </thead>
</table>

Now according to markup we need to modify our JSON response on server side ie, employee-grid-data.php where we need to add checkbox for individual row selection.

So here is our server side code:

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
// storing  request (ie, get/post) global array to a variable 
$requestData= $_REQUEST;
 
$columns = array(
// datatable column index  => database column name
    0 =>'employee_name',
    1 => 'employee_salary',
    2=> 'employee_age'
);
 
// getting total number records without any search
$sql = "SELECT id ";
$sql.=" FROM employee ";
$query=mysqli_query($conn, $sql) or die("employee-grid-data.php: get employees");
$totalData = mysqli_num_rows($query);
$totalFiltered = $totalData;  // when there is no search parameter then total number rows = total number filtered rows.
 
 
$sql = "SELECT id, employee_name, employee_salary, employee_age ";
$sql.=" FROM employee WHERE 1=1";
if( !empty($requestData['search']['value']) ) {   // if there is a search parameter, $requestData['search']['value'] contains search parameter
    $sql.=" AND ( employee_name LIKE '".$requestData['search']['value']."%' ";   
    $sql.=" OR employee_salary LIKE '".$requestData['search']['value']."%' ";
 
    $sql.=" OR employee_age LIKE '".$requestData['search']['value']."%' )";
}
$query=mysqli_query($conn, $sql) or die("employee-grid-data.php: get employees");
$totalFiltered = mysqli_num_rows($query); // when there is a search parameter then we have to modify total number filtered rows as per search result.
$sql.=" ORDER BY ". $columns[$requestData['order'][0]['column']]."   ".$requestData['order'][0]['dir']."  LIMIT ".$requestData['start']." ,".$requestData['length']."   ";
/* $requestData['order'][0]['column'] contains colmun index, $requestData['order'][0]['dir'] contains order such as asc/desc  */   
$query=mysqli_query($conn, $sql) or die("employee-grid-data.php: get employees");
 
$data = array();
$i=1+$requestData['start'];
while( $row=mysqli_fetch_array($query) ) {  // preparing an array
    $nestedData=array();
 
    $nestedData[] = "<input type='checkbox'  class='deleteRow' value='".$row['id']."'  /> #".$i ;
    $nestedData[] = $row["employee_name"];
    $nestedData[] = $row["employee_salary"];
    $nestedData[] = $row["employee_age"];
     
    $data[] = $nestedData;
    $i++;
}
 
 
 
$json_data = array(
            "draw"            => intval( $requestData['draw'] ),   // for every request/draw by clientside , they send a number as a parameter, when they recieve a response/data they first check the draw number, so we are sending same number in draw.
            "recordsTotal"    => intval( $totalData ),  // total number of records
            "recordsFiltered" => intval( $totalFiltered ), // total number of records after searching, if there is no searching then totalFiltered = totalData
            "data"            => $data   // total data array
            );
 
echo json_encode($json_data);  // send data as json format
 
?>

Now again back to index.php where we have to add some javascript code, for select / deselect all rows or select single row. After selection when a user will click the delete button, we will delete that/those rows through ajax. After ajax success we will again redraw datatable, so we can get our table data up to date.

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
$(document).ready(function() {
    var dataTable = $('#employee-grid').DataTable( {
        "processing": true,
        "serverSide": true,
        "columnDefs": [ {
              "targets": 0,
              "orderable": false,
              "searchable": false
                
            } ],
        "ajax":{
            url :"employee-grid-data.php", // json datasource
            type: "post",  // method  , by default get
            error: function(){  // error handling
                $(".employee-grid-error").html("");
                $("#employee-grid").append('<tbody class="employee-grid-error"><tr><th colspan="3">No data found in the server</th></tr></tbody>');
                $("#employee-grid_processing").css("display","none");
                 
            }
        }
    } );
     
     
    $("#bulkDelete").on('click',function() { // bulk checked
        var status = this.checked;
        $(".deleteRow").each( function() {
            $(this).prop("checked",status);
        });
    });
     
    $('#deleteTriger').on("click", function(event){ // triggering delete one by one
        if( $('.deleteRow:checked').length > 0 ){  // at-least one checkbox checked
            var ids = [];
            $('.deleteRow').each(function(){
                if($(this).is(':checked')) {
                    ids.push($(this).val());
                }
            });
            var ids_string = ids.toString();  // array to string conversion
            $.ajax({
                type: "POST",
                url: "employee-delete.php",
                data: {data_ids:ids_string},
                success: function(result) {
                    dataTable.draw(); // redrawing datatable
                },
                async:false
            });
        }
    });
} );

As we can see, we have also disabled search and sorting functionality in checkbox/ number index column.

1
2
3
4
5
6
"columnDefs": [ {
      "targets": 0,
      "orderable": false,
      "searchable": false
        
    } ]

we are fetching the ids of the selected checkbox and pushing into an array, and send it to employee-delete.php for deletion as a string.

In employee-delete.php , we again converting ids string to an array and deleting one by one by id.

Here is our employee-delete.php

1
2
3
4
5
6
7
8
9
$data_ids = $_REQUEST['data_ids'];
$data_id_array = explode(",", $data_ids);
if(!empty($data_id_array)) {
    foreach($data_id_array as $id) {
        $sql = "DELETE FROM employee ";
        $sql.=" WHERE id = '".$id."'";
        $query=mysqli_query($conn, $sql) or die("employee-delete.php: delete employees");
    }
}

And that’s all there is to it! You can download the working code from below link, Demo is development experimental and not a production ready.