Skip to main content

Create Ajax Pagination with PHP and MySQL

Updated on: coderszine team PHP, Tutorial

In our previous tutorial, we have explained how to implement jQuery Datatables Server Side Processing with PHP & MySQL. In this tutorial, we will explain how to implement Ajax pagination in your project with PHP and MySQL.

Pagination is a common feature in web projects to display large data on a single page. The pagination with ajax is more user friendly as its allows to display records on same page without reloading page.

So If you’re looking for solution to implement ajax pagination with PHP and MySQL, then you’re here at right place. We will cover this tutorial with live demo to implement ajax pagination with PHP and MySQL.

Also, read:

So let’s start to develop demo to implement Ajax pagination with PHP and MySQL. The file structure for the example is following.

  • index.php
  • data.php
  • ajax_pagination.js
  • style.css
Steps1: Create Database Table and Insert Data

As in this pagination example, we will display developer from MySQL database tables, so first we will create table developers using below table create query.

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,
  `image` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

We will also insert some records using below insert statement.

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

As we have handled this example with Bootstrap and jQuery, so we will include related files.

<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.3/jquery.min.js"></script>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/css/bootstrap.min.css">
<script type="text/javascript" src="ajax_pagination.js"></script>
<link rel='stylesheet' href='style.css' type='text/css' />
Steps3: Create Pagination Page HTML

In index.php, we will create pagination HTML and load developers records from MySQL datatable when page load.

<div class="container">	
	<h2>Create Ajax Pagination with PHP and MySQL</h2>
	<?php
	$per_page = 5;
	$sql = "SELECT id, name, gender, skills, address, designation, age FROM `developers`";
	$result = mysqli_query($conn, $sql);
	$count = mysqli_num_rows($result);
	$pages = ceil($count/$per_page)
	?>		
	<div id="content_container"></div>
	<div class="pagination">
		<ul id="paginate">
			<?php		
			for($i=1; $i<=$pages; $i++)	{
				echo '<li id="'.$i.'">'.$i.'</li>';
			}
			?>
		</ul>
	</div>
</div>
Steps4: Handle Pagination with Ajax

In ajax_pagination.php, we will handle to load pagination data with jQuery Ajax by making request to data.php.

$(document).ready(function(){	
	function hideLoading() {
		$("#loading").fadeOut('slow');
	};	
	$("#paginate li:first").css({'color' : '#FF0084', 'border' : 'none'});
	$("#content_container").load("data.php?page=1", hideLoading());
	$("#paginate li").click(function(){		
		$("#paginate li").css({'border' : 'solid #dddddd 1px', 'color' : '#0063DC'});
		$(this).css({'color' : '#FF0084', 'border' : 'none'});
		var page_num = this.id;
		$("#content_container").load("data.php?page=" + page_num, hideLoading());
	});
});
Steps5: Load Pagination Data

In data.php, we will get pagination data from MySQL database table according to Ajax request data and create HTML with data return back to display.

include("db_connect.php");
$per_page = 5;
if(isset($_GET['page'])) {
	$page=$_GET['page'];
}
$start = ($page-1)*$per_page;
$sql_query = "select id, name, gender, skills, address, designation, age FROM `developers` order by id limit $start,$per_page";
$resultset = mysqli_query($conn, $sql_query);
?>
<table width="100%">
	<thead>
		<tr>
		<th>   Id</th>
		<th>Name</th>
		<th>Age</th>
		<th>Gender</th>
		<th>Address</th>
		<th>Designation</th>
		<th>Skills</th>				
		</tr>
	</thead>
	<?php
		while($rows = mysqli_fetch_array($resultset)){	?>	
			<tr bgcolor="#DDEBF5">
			<td>   <?php echo $rows['id']; ?></td>
			<td><?php echo $rows['name']; ?></td>
			<td><?php echo $rows['gender']; ?></td>
			<td><?php echo $rows['age']; ?></td>
			<td><?php echo $rows['address']; ?></td>
			<td><?php echo $rows['designation']; ?></td>
			<td><?php echo $rows['skills']; ?></td>
			</tr>
	<?php }	?>
</table>

You may also like:

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

3 thoughts on “Create Ajax Pagination with PHP and MySQL”

  2. Nice article! This was a great article with lots of good examples and resources. This is really great article. This article was a lifesaver.

    Thanks.

  3. Great article. I really appreciate your help that’s really exactly what I was looking for, thank’s for that!

Comments are closed.