Optimizing Your Website with JQuery, MySQL, and PHP Pagination

When you are displaying any list of records, then it becomes necessary to display them in different pages instead of displaying on the single page as it will become long depending on the total numbers of the records. In this post we are going to learn about Pagination using jQuery, MySQL and PHP. We have below files:

config.php  - For the database connection details.
index.php  - Main file that display the result to the user.
data.php - File having code to fetch the data from the table.
pagination.js - Javascript file for acting as a data controller
style.css - For the css.


Create Database Table

 `FirstName` varchar(200) NOT NULL,
 `Middlename` varchar(200) NOT NULL,
 `LastName` varchar(200) NOT NULL,


Dump Data

INSERT INTO `users` (`id`, `FirstName`, `Middlename`, `LastName`) VALUES
(1, 'John', 'K', 'Doe'),
(2, 'Kevin', 'M', 'Smith'),
(3, 'Kane', 'K', 'Parle'),
(4, 'Novic', 'T', 'Ray'),
(5, 'Rod', 'H', 'Knee'),
(6, 'Jack', 'N', 'Ryder'),
(7, 'Rosita', 'W', 'Siegl'),
(8, 'Nina', 'R', 'Williams'),
(9, 'Anto', 'S', 'Velten');


config.php file

$hostname   = "localhost"; 
$username   = "root"; 
$password   = "password"; 
$database   = "test"; 
$connection = new mysqli($hostname, $username, $password, $database);

// Check connection
if ($connection->connect_errno) {
   echo "Failed to connect to MySQL: " . $connection->connect_error;


index.php file

Note : Here we are using select count(*) from table instead of select * from table because Letting MySQL do the counting is SIGNIFICANTLY faster,as it only counts the entries rather than parsing all data,AND only returns a very small block of data.

$per_page = 3; 

//getting number of rows and calculating no of pages
$sql    = "select count(*) from users";
$result = $connection->query($sql);
$count  = $result->fetch_row();
$pages  = ceil($count[0] / $per_page);
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en">
		<title>PHP Dev Zone : Pagination Using Mysql and jQuery </title>
		<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.3.0/jquery.min.js"></script>
		<script type="text/javascript" src="pagination.js"></script>	
		<link rel="stylesheet" href="style.css">
		<div align="center">
		    <div style="margin-top:50px;"><u><b><a href="https://www.php-dev-zone.com"> PHP Dev Zone </a></b>: Pagination Demo</u></div>
		 	<div id="content" ></div>
			<table width="800px">
						<ul id="pagination">
							//Show page links
							for ($i = 1; $i <= $pages; $i++) {
								echo '<li id="'.$i.'">'.$i.'</li>';
							} ?>
			<div id="loading"></div>


data.php file

$per_page = 3; 
$page     = (isset($_GET['page']) && is_numeric($_GET['page'])) ? $_GET['page'] : 1;

//getting table contents
$start = ($page-1)*$per_page;
$sql   = "select * from users order by id limit $start,$per_page";
$rsd   = $connection->query($sql);
<table id="tbl">
	while ($row = $rsd->fetch_array()) {
		$id    = $row['id'];
		$fname = $row['FirstName'];
		$mname = $row['Middlename'];
		$lname = $row['LastName'];
		<td><?php echo $id; ?></td>
		<td><?php echo $fname; ?></td>
		<td><?php echo $mname; ?></td>
		<td><?php echo $lname; ?></td>
	<?php } ?>


pagination.js file

$(document).ready(function() {
	//Loading Image Display
	function Display_Load() {
		$("#loading").html("<img src='loading.gif' />");
	//Hide Loading Image
	function Hide_Load() {
    //Default Starting Page Results
	$("#pagination li:first").css({'color' : '#FF0084','border' : 'none'});
	$("#content").load("data.php?page=1", Hide_Load());

	//Pagination Click
	$("#pagination li").click(function() {
		//CSS Styles
		$("#pagination li")
		.css({'border' : 'solid #dddddd 1px'})
		.css({'color' : '#0063DC'});
		.css({'color' : '#FF0084'})
		.css({'border' : 'none'});

		//Loading Data
		var pageNum = this.id;
		$("#content").load("data.php?page=" + pageNum, Hide_Load());


style.css file

body {margin:0;padding:0;font-family:Verdana;font-size:15px}
a {text-decoration:none;color:#b2b2b2}
a:hover {color:#df3d82;text-decoration:underline}
#loading {width:100%;position:absolute}
#pagination {text-align:center;margin-left:120px}
li {list-style:none;float:left;margin-right:16px;padding:5px;border:solid 1px #ddd;color:#0063dc}
li:hover {color:#ff0084;cursor:pointer}
#tbl {width:800px;border:1px solid #98bf21;margin-top:50px}
#tbl tr:nth-child(odd) {background:#eaf2d3}
#tbl td {border:1px solid #98bf21}
#tbl th {background:#a7c942;border:1px solid #98bf21}


Download Source


Ketan Patel

As a backend and ecommerce developer, I have extensive experience in implementing robust and scalable solutions for ecommerce websites and applications. I have a deep understanding of server-side technologies and have worked with various programming languages. I have experience in integrating with payment gateways, and other third-party services.