Datatable Bulk Delete (Server Side) Part:10

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.

	<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:

// 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.


			$(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.

	"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


$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.

DownloadDemo


  • Miku Loder

    Great tutorial! Thank you Arkaprava. You helped me a lot.

  • Eric

    Great tutorials! You’ve helped me get started on a project and cut my work load down considerably. I was wondering if you have any experience with using any inline editing plugins for datatables such as the jQuery Datatables Data Manager found here https://code.google.com/p/jquery-datatables-editable/. I’m running into many issues with I believe incompatible versions of the plugins with jQuery and datatables.

  • CLAUDIO RODRIGUEZ ORE

    This tutorial is more useful to change states not to delete records

  • Rudi Giat

    hii..
    can you write tutorial edit delete in line with data table please
    Thanks

  • Dii Doo

    Thank you for This Tutorial, please can do the one that has inline edit button? Thanks

  • Bell

    Thank you for the great work, very helpful!.

    I vote with others for inline edit/bulk update tutorial!

    And for complete recipe in part 12,

    – Ability to add individual row and save to existing data. [easy]

    – import from CSV. [easy]

    – inline edit/bulk update [needs experience].

    Then, for part 13, we can go for data statistics && cool charts!

    Something like that

    http://evoluteur.github.io/evolutility/demo/index.html

    or even another tutorial series, Ajax & PHP MySQL backend to Evolutility itself.

  • Really helpful..thanx

  • Hamis Matangwa

    Very helpful tutorial!!!
    Also can you do a confirmation on Delete? please

  • Alin Ambrosie

    Hi,
    Is there a method to select all rows not only from current page?

  • Sanooj T

    Hi all please check the blog related to this article http://opensourceblog.in/datatable-server-side-advanced-options/

  • Leoj Calimlim

    Hi do you have pdo connection for this tutorial?
    anyway thanks for this Great tutorial!

  • dev smashes

    Hi I didn’t get any records after adding checkbox and the same code I followed….. https://uploads.disquscdn.com/images/aa4412b48f637acc03c91c9e4adf074334caec3d168b5f325ade8ca9a51eeb1d.png