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.

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

In this post, we have explained how to handle server side processing of Datatables 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 
ie("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