Load and Refresh jQuery DataTable with PHP

As we know that the Datatable is a highly flexible jQuery plugin to convert HTML table into useful grid layout. The Datatable helps to create Data Table into full functional data grid with features like Pagination, instant search, export table data and multi-column ordering and can be easily used with both Bootstrap and jQuery UI.

We often need to refresh our Datatable with interval of time to display updated data. But when we refresh Datatbale with Datatable ajax.reload(), it redraw Datatable by losing current page ordering, pagination and back to the first page. So in this post, we have handled how to load jQuery Datatable and refresh jQuery Datatable data on every 5 seconds interval without loosing current page ordering and pagination.

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 according to jQuery Datatable structure.
<table id="example" class="display" width="100%" cellspacing="0">
<thead>
<tr>
<th>Empid</th>
<th>Name</th>
<th>Salary</th>
</tr>
</thead>
</table>


Steps3:Here 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 to load to data table.
$( document ).ready(function() {
var table = $('#example').DataTable( {
"ajax": "data.php",
"bPaginate":true,
"bProcessing": true,
"pageLength": 5,
"columns": [
{ mData: 'Empid' } ,
{ mData: 'Name' },
{ mData: 'Salary' }
]
});
});


Steps4: Here we will update Datatable data on every 5 seconds interval by making ajax request using Datatable function ajax.reload(). Here I have passed second parameter as false to remain on current page to not loose pagination when update or refresh Datatable.
setInterval( function () {
table.ajax.reload(null, false);
}, 5000 );


Steps5: Now finally we will get data from MySQL database and returned as JSON through PHP function json_encode with Datatable plugin options.
<?php
$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]