Datatable Server Side Processing with PHP

Datatable is a highly flexible jQuery plugin that’s used to convert HTML table into useful grid layout. This plugin enables to create Data Table into full functional data grid with features like Pagination, instant search, export table data and multi-column ordering. It can be easily used with both Bootstrap and jQuery UI.



In this post, we have explained how to handle server side processing of Datatable with PHP.

So let’s start the coding

Steps1: First we will include jquery datatable and jquery library files.
<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.3/jquery.min.js"></script>
<link rel="stylesheet" type="text/css" href="//cdn.datatables.net/1.10.12/css/jquery.dataTables.css">
<script type="text/javascript" charset="utf8" src="//cdn.datatables.net/1.10.12/js/jquery.dataTables.js"></script>


Steps2: Now we will create datatable HTML to display data in it.
<table id="datatable_example" class="display" width="100%" cellspacing="0">
<thead>
<tr>
<th>Empid</th>
<th>Name</th>
<th>Salary</th>
</tr>
</thead>
</table>



Steps3:Now we will handle datatable functionality using jQuery datatable plugin by making an ajax request to server side data.php to get data from MySQL database table.
$( document ).ready(function() {
var table = $('#datatable_example').dataTable({
"bProcessing": true,
"sAjaxSource": "data.php",
"bPaginate":true,
"sPaginationType":"full_numbers",
"iDisplayLength": 5,
"aoColumns": [
{ mData: 'Empid' } ,
{ mData: 'Name' },
{ mData: 'Salary' }
]
});
});



Steps4: Finally at server side in data.php, we will get data from MySQL database and returned as JSON through php function json_encode with datatable plugin options.
<?php
$servername = "localhost";
$username = "root";
$password = "12345";
$dbname = "demos";
$sql = "SELECT id as Empid,employee_name as Name,employee_salary as Salary FROM employee LIMIT 20";
$resultset = mysqli_query($conn, $sql) or die("database error:". mysqli_error($conn));
$data = array();
while( $rows = mysqli_fetch_assoc($resultset) ) {
$data[] = $rows;
}
$results = array(
"sEcho" => 1,
"iTotalRecords" => count($data),
"iTotalDisplayRecords" => count($data),
"aaData"=>$data);
echo json_encode($results);
?>


You can view the live demo from the Demo link and can download the script from the Download link below.
Demo [sociallocker]Download[/sociallocker]