PHP MySQL Pagination using jQuery Ajax Example

PHP mysql jquery ajax pagination. Through this tutorial, you will learn how to create jQuery ajax pagination with PHP + MySQL without reload or refresh the whole web page.

Sometimes, your MySQL database contains thousands of records; And you are showing a list of them from MySQL database in the PHP app and you want to see only a few records and data on one page. So for this, you can show the data by using Pagination in Php Mysql with Jquery Ajax.

Pagination in PHP and MySQL with Ajax and jQuery

Follow the below steps and implement advanced jQuery ajax pagination + bootstrap with PHP and MySQL;

Step 1 – Create Database And Table
Step 2 – Create a Database Connection File
Step 3 – Download simple-bootstrap-paginator Plugin
Step 4 – Create List File
Step 5 – Create Get Data PHP File

Step 1 – Create Database And Table

First of all, navigate to your phpmyadmin panel and create database and table using the following sql queries:

CREATE DATABASE my_db;

CREATE TABLE `users` (
  `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;

Then insert few records into users table to display records with pagination; so use the following SQL query for that:

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

Step 2 – Create a Database Connection File

In this step, you will create a file name db.php and add the following code into your file.

The below code is used to create a MySQL database connection in PHP. When you insert form data into MySQL database, there you will include this file:

<?php
	$servername='localhost';
	$username='root';
	$password='';
	$dbname = "my_db";
	$conn=mysqli_connect($servername,$username,$password,"$dbname");
	  if(!$conn){
		  die('Could not Connect MySql Server:' .mysql_error());
		}
?>

Step 3 – Download simple-bootstrap-paginator Plugin

In this step, download the jQuery simple bootstrap pagination plugin and place it into your PHP project directory. Then include Bootstrap, jQuery and pagination plugin files into your list.php file.

Step 4 – Create List File

In this step, you need to create a file which names list.php and add the following code into it:

<!DOCTYPE html>
<html>
<head>
	<title>php mysql jquery ajax pagination - Tutsmake</title>
	<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>
</head>
<body>
<div class="container">
	<?php
		include_once("db.php");
		$perPage = 5;
		$sqlQuery = "SELECT * FROM users";
		$result = mysqli_query($conn, $sqlQuery);
		$totalRecords = mysqli_num_rows($result);
		$totalPages = ceil($totalRecords/$perPage);
	?>
	<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>
<script>
	$(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:"get_data.php",
				method:"POST",
				dataType: "json",
				data:{page:	page},
				success:function(responseData){
					$('#content').html(responseData.html);
				}
			});
		}
	});
});
</script>
</body>
</html>

Step 5 – Create Get Data PHP File

In this step, you need to create a new file name get_data.php and add the below code into your get_data.php file.

<?php
include_once("db.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 users 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);
?>

The above below code is used to fetch data from MySQL database using PHP server. And return data to HTML page using jQuery ajax pagination + bootstrap library.

Conclusion

PHP MySQL jquery ajax pagination. Through this tutorial, you have learned how to create jQuery ajax pagination with PHP + MySQL without reload or refresh the whole web page.

One reply to PHP MySQL Pagination using jQuery Ajax Example

Avinash Sharma

3 years ago

Very attractive blog. Thank dear for much for giving this Information. This information is very helpful for all web Developers and designers. You really work hard i appreciate your work.

Subscribe to Blog via Email