DataTable Pagination using PHP & MySQL

In a series of jQuery Datatable tutorial, you have leanred about Datatable server side processing using PHP and how to load jQuery Datatable and refresh jQuery Datatable data. Today in this tutorial, you will learn how create jQuery Datatable pagination using PHP and MySQL. The tutorial is explained in very easy steps with live demo and can download demo source code.



So let’s start the coding. We will have following file structure for data scraping tutorial

• index.php


• db_connect.php


• pagination_data.php


• paginate.js

Steps1: For pagination example, we will load data from MySQL database, so we need to create Database Table to store and get data.
CREATE TABLE `employee` (
`id` int(11) NOT NULL COMMENT 'primary key',
`employee_name` varchar(255) NOT NULL COMMENT 'employee name',
`employee_salary` double NOT NULL COMMENT 'employee salary',
`employee_age` int(11) NOT NULL COMMENT 'employee age'
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


Steps2: After creating MySQL database table, we will create db_connect.php file to make connection with MySQL database.
<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "phpzag_demos";
$conn = mysqli_connect($servername, $username, $password, $dbname);
?>



Steps3: Now 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>



Steps4: In index.php, we will create datatable HTML to display data in it.

<div class="container">
<h2>jQuery DataTable Pagination using PHP & MySQL</h2>
<div class="row">
<table id="example" class="display" width="100%" cellspacing="0">
<thead>
<tr>
<th>Empid</th>
<th>Name</th>
<th>Salary</th>
</tr>
</thead>
</table>
</div>
<div style="margin:50px 0px 0px 0px;">
<a class="btn btn-default read-more" style="background:#3399ff;color:white" href="https://www.phpzag.com/datatable-pagination-using-php-mysql" title="">Back to Tutorial</a>
</div>
</div>


Steps5: Now in paginate.js, we will create data table using jQuery datatable plugin and make an ajax request to server side pagination_data.php to get data from MySQL database table to display in Datatable. We will use bPaginate:true to create pagination, iDisplayLength: 5 to display 5 records at a time and sPaginationType:"full_numbers" to display full pagination options.

jQuery( document ).ready(function() {
var table = jQuery('#example').dataTable({
"bProcessing": true,
"sAjaxSource": "pagination_data.php",
"bPaginate":true,
"sPaginationType":"full_numbers",
"iDisplayLength": 5,
"bLengthChange":false,
"bFilter": false,
"aoColumns": [
{ mData: 'Empid' } ,
{ mData: 'Name' },
{ mData: 'Salary' }
]
});
});



Steps6: Finally in pagination_data.php, we will get data from MySQL database and returned as JSON through php function json_encode with datatable plugin pagination options. We will return iTotalRecords and iTotalDisplayRecords values to for jQuery Datatable pagination.
<?php
include_once("db_connect.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);
exit;
?>



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