Advanced Ajax Pagination with PHP and MySQL

Pagination is an important feature of web applications to display huge data in chunks of pages instead of displaying all records. With pagination, we divide total records into chunks of pages with small number of record. We mostly loads pagination pages on each page request with same page reload. But now the ajax pagination is more improved and more user friendly which allow users to load next or previous pages without reloading page.

So if you’re a PHP developer and thinking to implement advanced ajax pagination with PHP, then you’re here at right place. in this tutorial you will learn how to implement ajax pagination with PHP and MySQL. In this tutorial we will use simple bootstrap pagination plugin to create advance pagination.

We will cover this tutorial in easy steps to live example to create advanced ajax pagination with PHP and MySQL. We will also allow you to download complete source code of live example.

Also, read:

• Ajax Pagination in CodeIgniter with Example

So let’s start implementing advance ajax pagination with PHP and MySQL. Before we begin, take a look on files structure for this example.

• index.php
• pagination.js
• load_data.php

Step1: Create MySQL Database Table

As we will display developer data with pagination, so first we will create MySQL database table developers to store records.

CREATE TABLE `developers` (
  `id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `skills` varchar(255) NOT NULL,
  `address` varchar(255) NOT NULL,
  `gender` varchar(255) NOT NULL,
  `designation` varchar(255) NOT NULL,
  `age` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

We will also insert few records into developers table to display records with pagination.

INSERT INTO `developers` (`id`, `name`, `skills`, `address`, `gender`, `designation`, `age`) VALUES
(1, 'Smith', 'Java', 'Newyork', 'Male', 'Software Engineer', 34),
(2, 'David', 'PHP', 'London', 'Male', 'Web Developer', 28),
(3, 'Rhodes', 'jQuery', 'New Jersy', 'Male', 'Web Developer', 30),
(4, 'Sara', 'JavaScript', 'Delhi', 'Female', 'Web Developer', 25),
(5, 'Shyrlin', 'NodeJS', 'Tokiyo', 'Female', 'Programmer', 35),
(6, 'Steve', 'Angular', 'London', 'Male', 'Web Developer', 28),
(7, 'Cook', 'MySQL', 'Paris', 'Male', 'Web Developer', 26),
(8, 'Root', 'HTML', 'Paris', 'Male', 'Web Developer', 28),
(9, 'William', 'jQuery', 'Sydney', 'Male', 'Web Developer', 23),
(10, 'Nathan', 'PHP', 'London', 'Male', 'Web Developer', 28),
(11, 'Shri', 'PHP', 'Delhi', 'Male', 'Web Developer', 38),
(12, 'Jay', 'PHP', 'Delhi, India', 'Male', 'Web Developer', 30);

Step2: Include Bootstrap, jQuery and Pagination Plugin File

As we will use Bootstrap design and also jQuery simple bootstrap pagination plugin, so in index.php, we will include Bootstrap, jQuery and pagination plugin files.

<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/css/bootstrap.min.css">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/css/bootstrap-theme.min.css">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.3/jquery.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/js/bootstrap.min.js"></script>
<script src="plugin/simple-bootstrap-paginator.js"></script>
<script src="js/pagination.js"></script>

Step3: Create Bootstrap HTML Table with Content Container

In index.php file, we will create Bootstrap HTML table to display records through Ajax request. We will also create pagination container to display pagination with buttons.

<div class="container">	
	<div class="row">
		<table class="table table-hover table-bordered">
			<thead>
				<tr>
					<th>Id</th>
					<th>Name</th>
					<th>Age</th>
					<th>Address</th>
					<th>Skills</th>
					<th>Designation</th>
				</tr>
			</thead>
			<tbody id="content">     
			</tbody>
		</table>   
		<div id="pagination"></div>    
		<input type="hidden" id="totalPages" value="<?php echo $totalPages; ?>">	
	</div>    
</div>

We will also get the total the pages to display according to total records from MySQL database table developers. Here in this example, we will display 5 records per page.

<?php
include_once("db_connect.php");
$perPage = 5;
$sqlQuery = "SELECT * FROM developers";
$result = mysqli_query($conn, $sqlQuery);
$totalRecords = mysqli_num_rows($result);
$totalPages = ceil($totalRecords/$perPage)
?>

Step4: Load Pagination Data with Ajax Request

As here in this example we are handling pagination with simple-bootstrap-paginator plugin, so on pageChange event, we will handle Ajax request in pagination.js to load pagination data from MySQL database by making Ajax request to server side php script load_data.php. We will get response as JSON data and set response JSON HTML to display pagination records.

$(document).ready(function(){
	var totalPage = parseInt($('#totalPages').val());	
	var pag = $('#pagination').simplePaginator({
		totalPages: totalPage,
		maxButtonsVisible: 5,
		currentPage: 1,
		nextLabel: 'Next',
		prevLabel: 'Prev',
		firstLabel: 'First',
		lastLabel: 'Last',
		clickCurrentPage: true,
		pageChange: function(page) {			
			$("#content").html('<tr><td colspan="6"><strong>loading...</strong></td></tr>');
            $.ajax({
				url:"load_data.php",
				method:"POST",
				dataType: "json",		
				data:{page:	page},
				success:function(responseData){
					$('#content').html(responseData.html);
				}
			});
		}
	});
});

Step5: Load Pagination Records from MySQL Database

In load_data.php, we will get records from MySQL database table developers according to pagination data request and return data as JSON.

<?php
include_once("db_connect.php");
$perPage = 5;
if (isset($_GET["page"])) { 
	$page  = $_GET["page"]; 
} else { 
	$page=1; 
};  
$startFrom = ($page-1) * $perPage;  
$sqlQuery = "SELECT id, name, age, address, skills, designation
	FROM developers ORDER BY id ASC LIMIT $startFrom, $perPage";  
$result = mysqli_query($conn, $sqlQuery); 
$paginationHtml = '';
while ($row = mysqli_fetch_assoc($result)) {  
	$paginationHtml.='<tr>';  
	$paginationHtml.='<td>'.$row["id"].'</td>';
	$paginationHtml.='<td>'.$row["name"].'</td>';
	$paginationHtml.='<td>'.$row["age"].'</td>'; 
	$paginationHtml.='<td>'.$row["address"].'</td>';
	$paginationHtml.='<td>'.$row["skills"].'</td>';
	$paginationHtml.='<td>'.$row["designation"].'</td>'; 
	$paginationHtml.='</tr>';  
} 
$jsonData = array(
	"html"	=> $paginationHtml,	
);
echo json_encode($jsonData); 
?>

You may also like:

• User Management System with PHP & MySQL
• Datatables Add Edit Delete with Ajax, PHP & MySQL
• Build Helpdesk System with jQuery, PHP & MySQL
• Build Online Voting System with PHP & MySQL
• School Management System with PHP & MySQL
• DataTables Add Edit Delete with CodeIgniter
• Create RESTful API using CodeIgniter
• Build Reusable Captcha Script with PHP
• Product Search Filtering using Ajax, PHP & MySQL
• Image Upload and Crop in Modal with jQuery, PHP & MySQL
• Build Push Notification System with PHP & MySQL
• Project Management System with PHP and MySQL
• Hospital Management System with PHP & MySQL
• Build Newsletter System with PHP and MySQL
• Skeleton Screen Loading Effect with Ajax and PHP
• Build Discussion Forum with PHP and MySQL
• Customer Relationship Management (CRM) System with PHP & MySQL
• Online Exam System with PHP & MySQL
• Expense Management System with PHP & MySQL

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