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.
Also, read:
- Export jQuery Datatable Data To PDF, Excel, CSV & Copy with PHP
- DataTable Pagination using PHP & MySQL
- Create Advance Pagination with PHP and MySQL
- Create Bootstrap Table Pagination with jQuery
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 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 may also like:
- Working with php.ini file Configuration
- Control Statements in PHP
- Convert Associative Array into XML in PHP
- Convert XML into Associative Array in PHP
- Using Prepared Statement with PHP & MySQL
- How to Upload File in PHP
- Converting an Array to JSON in PHP
- Converting JSON to Array or Object in PHP
- Manipulating PHP arrays: push, pop, shift, unshift
- Remove Repeated Words From String in PHP
- Converting a PHP Array to a Query String
- 15+ regular expressions for PHP developers
- 10 Most Important Directory Functions in PHP
- 10 little known but useful PHP functions
- PHP Script to Download Large Files Reliably
You can view the live demo from the Demo link and can download the script from the Download link below.
Demo Download