Ajax Pagination with Tabular Records using PHP and jQuery
by Vincy. Last modified on July 3rd, 2022.
For an improved version of this code, visit PHP Pagination MySQL Database Example Script with Previous Next like Google.
Pagination feature is for limiting the number of results instead of loading all in a list page. Loading multiple records with pagination will increase efficiency to load result page by page.
We have seen several examples for PHP pagination with database results. For the pagination functionality, a perpage result count will be configured. This configured number will be used in the query to set the limit.
Pagination functionality will be suitable when we need to show more result in a list, tabular form, gallery and more. In this example, we are going to learn how to apply pagination for the tabular records resulted in by querying the database.
I have used jQuery AJAX for displaying the paginated result in a tabular form. The perpage count is configured as 5 to fetch five records from the database by setting the query limit. The number of pages is calculated from the total record count and the perpage configuration.
This screenshot shows the output of the PHP AJAX pagination for tabular records.
HTML Code to Display Paginated Tabular Records
This code shows the target container in the HTML to display the paginated result in a tabular form. On loading the landing page, an AJAX call will be sent to the PHP by passing the start and the limit parameters to query the database.
In this example, the staff data are stored in the database and queried via AJAX to display the paginated results from the tbl_staff.
<html>
<head>
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<script
src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.3/jquery.min.js"></script>
<title>Ajax Pagination with Tabular Records using PHP and jQuery</title>
<link rel="stylesheet" href="style.css" type="text/css" />
</head>
<body>
<div id="container">
<div id="inner-container">
<div id="results"></div>
<div id="loader"></div>
</div>
</div>
</body>
</html>
AJAX Script to Access PHP to Query Pagination Results
This jQuery AJAX script is used to call PHP file getPageDate.php to fetch limited records from the database as specified. On sending AJAX request the start param will be posted to the PHP to set the query limit with perpage configuration.
The resultant HTML response will be received on the AJAX success callback to update the UI with the paginated result.
<script type="text/javascript">
function showRecords(perPageCount, pageNumber) {
$.ajax({
type: "GET",
url: "getPageData.php",
data: "pageNumber=" + pageNumber,
cache: false,
beforeSend: function() {
$('#loader').html('<img src="loader.png" alt="reload" width="20" height="20" style="margin-top:10px;">');
},
success: function(html) {
$("#results").html(html);
$('#loader').html('');
}
});
}
$(document).ready(function() {
showRecords(10, 1);
});
</script>
PHP Code to Fetch Staff Details with Pagination
This is the PHP code that is called via AJAX. It receives the query limit params to set the start and limit value of the select query to retrieve the limited record from the database. After getting the results, it forms HTML response to display tabular results with pagination links.
The pagination links are created in a loop which is executed until the total page count is reached. In this example, the MySQLi functions are used to query with the database. You can find PHP pagination example with PDO if you need.
<?php
require_once ("db.php");
if (! (isset($_GET['pageNumber']))) {
$pageNumber = 1;
} else {
$pageNumber = $_GET['pageNumber'];
}
$perPageCount = 5;
$sql = "SELECT * FROM tbl_staff WHERE 1";
if ($result = mysqli_query($conn, $sql)) {
$rowCount = mysqli_num_rows($result);
mysqli_free_result($result);
}
$pagesCount = ceil($rowCount / $perPageCount);
$lowerLimit = ($pageNumber - 1) * $perPageCount;
$sqlQuery = " SELECT * FROM tbl_staff WHERE 1 limit " . ($lowerLimit) . " , " . ($perPageCount) . " ";
$results = mysqli_query($conn, $sqlQuery);
?>
<table class="table table-hover table-responsive">
<tr>
<th align="center">Name</th>
<th align="center">Experience<br>(in years)
</th>
<th align="center">Subject</th>
</tr>
<?php foreach ($results as $data) { ?>
<tr>
<td align="left">
<?php echo $data['name'] ?>
</td>
<td align="left">
<?php echo $data['experience'] ?>
</td>
<td align="left">
<?php echo $data['major'] ?>
</td>
</tr>
<?php
}
?>
</table>
<div style="height: 30px;"></div>
<table width="50%" align="center">
<tr>
<td valign="top" align="left"></td>
<td valign="top" align="center">
<?php
for ($i = 1; $i <= $pagesCount; $i ++) {
if ($i == $pageNumber) {
?> <a href="javascript:void(0);" class="current">
<?php echo $i ?>
</a> <?php
} else {
?> <a href="javascript:void(0);" class="pages"
onclick="showRecords('<?php echo $perPageCount; ?>', '<?php echo $i; ?>');">
<?php echo $i ?>
</a> <?php
} // endIf
} // endFor
?>
</td>
<td align="right" valign="top">Page <?php echo $pageNumber; ?>
of <?php echo $pagesCount; ?>
</td>
</tr>
</table>
Written by Vincy, a web developer with 15+ years of experience and a Masters degree in Computer Science. She specializes in building modern, lightweight websites using PHP, JavaScript, React, and related technologies. Phppot helps you in mastering web development through over a decade of publishing quality tutorials.
Comments to “Ajax Pagination with Tabular Records using PHP and jQuery”
Comments are closed.
Hi, I'm Vincy. I help build websites, grow businesses, big and small. If that is what you want, contact me.
I'm currently available for freelance work.
Nice tutorial and very easy to integrate, But i have one question: i have a lot of records in my database table, so after implementing this code, the pagination shows up to 70 buttons on the footer part of the table, please how do i show just few buttons on the pagination or just Previous/Next button instead ?
Thank you Johnson. I am publishing a post on pagination in a week. It will have comprehensive features and that includes the few buttons with previous and next option.