datatables ajax

10 Easy ways to learn jQuery Datatables – Part 2

In my previous post, we have discussed the Basic jQuery Datatables with static data, this post we will be discussing Server-Side Ajax calls & Pagination. As you already aware of preloading the data from the database, but the main problem is bandwidth which will crash server & take the time to load the data in client side.

To overcome bandwidth issue, we will load the data from Ajax call from jQuery Datatables it will reduce the bandwidth & load the data fast manner on the client side.

Let’s create two files for Client & Server Side.

<html>
<head>
<title>10 Easy way to learn jQuery Datatables - Part 2</title>

<link rel="stylesheet" type="text/css" href="//cdn.datatables.net/1.10.12/css/jquery.dataTables.min.css">

<script src="//cdnjs.cloudflare.com/ajax/libs/jquery/3.1.0/jquery.min.js"></script>
<script src="//cdn.datatables.net/1.10.12/js/jquery.dataTables.min.js"></script>
 

</head>

<body>
    <table id="example" class="row-border" cellspacing="0" width="100%">
		<thead><tr><th>Name</th><th>Position</th><th>Office</th><th>Age</th><th>Start date</th><th>Salary</th></tr></thead>
		<tfoot><tr><th>Name</th><th>Position</th><th>Office</th><th>Age</th><th>Start date</th><th>Salary</th></tr></tfoot>
	</table>
	
<script>
$(document).ready(function(){
	$('#example').DataTable({
		"serverSide":true,
		"sPaginationType": "full_numbers",
		"bSort" : false,
		"sAjaxSource": "server-side.php?r="+Math.random(),
	});
});
</script>
</body>
</html>

We have included supporting CSS & JS file to load the jQuery Datatables, then created a table called “example”. Finally, we are calling the sAjaxSource method which will fetch server side data, we have enabled serverSide & sPaginationType for the number format.

So whenever you refresh the page or navigate the pagination data will fetch from server side without refreshing the web page. Also, it will reduce the traffic between client and server, because we have set limit in our query execution side.

Now let’s check our server-side file.

<?php

$arrData = array();
$mysqli = new mysqli("localhost","root","","demo_cs");

// Getting the count of the jobs table's data
$resultTotalCount = $mysqli->query("select count(ID) cnt from jobs");
$row = $resultTotalCount->fetch_array(MYSQLI_ASSOC);

// setting up the offset value 
$iStart = ($_GET['iDisplayStart']=='0') ? 	0 : ($_GET['iDisplayStart']+1);

// default limit assigned as 10, it will display 10 records in each page
$iLimit = 10;

// executing the query based on the iStart varaible with iLimit
$result = $mysqli->query("select Name,Postion,Office,Age,StartDate,Salary from jobs limit ".$iStart.",".$iLimit);
while($rs = $result->fetch_array(MYSQLI_ASSOC))
{
	// assigning the data into arrData variable	
	$arrData['aaData'][] =  array($rs['Name'],$rs['Postion'],$rs['Office'],$rs['Age'],$rs['StartDate'],$rs['Salary']);
}

// assigning the total display & records count for the pagination
$arrData['iTotalDisplayRecords'] = $arrData['iTotalRecords'] = isset($row['cnt']) ? $row['cnt'] : 0;

// encoding the data into json format
header("Content-Type: application/json; charset=utf-8");
echo json_encode($arrData);

Our server side script displays the data in JSON format, based on the column added in HTML table it will display the data in jQuery Datatables.