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.

So here 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.

Also, read:

• Export jQuery Datatable Data To PDF, Excel, CSV & Copy with PHP
• Create Advance Pagination with PHP and MySQL
• Create Bootstrap Table Pagination with jQuery

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 may also like:

• Star Rating System with Ajax, PHP and MySQL
• Create Event Calendar with jQuery, PHP and MySQL
• Build Your Own CAPTCHA Script with PHP
• Convert Unix Timestamp To Readable Date Time in PHP
• Inventory Management System with Ajax, PHP & MySQL
• Create Live Editable Table with jQuery, PHP and MySQL
• Live Add Edit Delete datatables Records with Ajax, PHP and MySQL
• Stripe Payment Gateway Integration in PHP
• Export Data to Excel with PHP and MySQL
• Star Rating System with Ajax, PHP and MySQL
• Create Dynamic Bootstrap Tabs with PHP & MySQL
• How To Create Simple REST API in PHP

You can view the live demo from the Demo link and can download the script from the Download link below.
Demo Download