PHP Pagination Code with Search using AJAX
by Vincy. Last modified on July 13th, 2022.
Fetching and displaying a bulk of records on a single fetch is not a good practice. It will increase the server load and has more disadvantages.
Pagination will resolve this problem.
Most of the website shows links of numbers for pagination. There are different ways to add pagination. Some of them are listed below.
- Previous-next navigation controls.
- Number links 1, 2, 3 … up to … total number of pages.
- A restricted number of page links that are expandable on navigation.
In this tutorial, we are going to see an example of a PHP pagination code with search sorting.
This example adds pagination to a list of records displayed in a tabular view. It uses jQuery AJAX to fetch records for each page from the database.
What is pagination
- The Pagination is a simple method to split voluminous data into pages. It prevents the disadvantages of displaying all records on a page.
- It shows a limited number of records per page which lets a quick glance at the page result.
- Paginated results require the start offset and per-page count to set the LIMIT to fetch.
Merits and demerits of pagination
There are more merits of using PHP pagination. Some of them are listed below.
- It increases efficiency by loading the page content quickly.
- It reduces server overload.
- The pagination links will reveal the approximate data stuff of the application.
- It will greatly improve site responses on displaying the media library with pagination.
- It increases user experience in browsing the paginated results.
Almost PHP pagination has no demerits, but it is not suitable in some scenarios. For example,
- It is skippable for applications displaying records that are countable by fingers.
- Without relational attributes (like prev, next URL), it gives negative impacts on SEO’s point of view.
About this example
This example has the code to group the pagination, sorting, search for a listing page.
The pagination UI will show limited pagination links. It extends the link stack on moving forward page by page.
In this example, we have three options based on which pagination format will be changed.
The pagination request carries over the search and sorting parameters if exist.
It calls an AJAX script to send the pagination, search and sort request data to the PHP.
It allows to search and sort by name via AJAX. The default sorting order is ascending (ASC) to sort the name column in alphabetical order.
The on-click event of the column header switches the sorting orders between ‘ASC’ and ‘DESC’
File structure
This is the file structure of the PHP pagination example. The has the paginated results to be rendered into the home page.
The list.php is to display the results queried with a per-page limit. The perpage.php file contains the handlers to prepare the pagination HTML to be shown into the UI.
Home page HTML to render pagination UI
The following code shows the HTML to display the member database results.
It displays a name-wise search panel above the table header. Also, the first column displays the member names with a sorting feature.
The PHP pagination, search and sorting actions give results without page refresh. It uses jQuery AJAX to request PHP to perform these actions without reloading the page.
The “listViaAJAX()” JavaScript function request PHP pagination result via AJAX. The “sortList()” function, sets the ordering request parameters to get the sorted results.
The search form submit-button invokes the listViaAJAX() code to send the keyword. In the callback, it gets the response HTML from the server and updates the UI.
index.php
<html>
<head>
<link href="./assets/style.css" type="text/css" rel="stylesheet" />
</head>
<body>
<h2 class="page-title">PHP Pagination with Search</h2>
<div id="grid">
<form name="frmSearch" id="frmSearch" method="post" action="index.php">
<div class="search-box">
<label>Search :</label> <input type="text" id="search"
placeholder="name" name="filter_value" class="demoInputBox" /> <input
type="button" name="go" class="search-form-btn" value="Search"
onClick="listViaAJAX();"> <input type="reset"
class="search-form-btn" value="Reset"
onclick="window.location='index.php'">
</div>
<table id=mytable cellpadding="10" cellspacing="1">
<thead>
<tr>
<th class="sortable"><span onClick="sortList();"><input
type="hidden" name="order_type" id="order-type" value="asc" />Name</span>
<img src='images/sort-down.svg' /></th>
<th>Registered Date</th>
<th class="align-right">Subscription Amount</th>
</tr>
</thead>
<tbody id="table-body">
<?php
require_once __DIR__ . '/list.php';
?>
<tbody>
</table>
</form>
</div>
</body>
<script src="https://code.jquery.com/jquery-3.6.0.min.js"
integrity="sha256-/xUj+3OJU5yExlq6GSYGSHk7tPXikynS7ogEvDej/m4="
crossorigin="anonymous"></script>
<script src='./assets/pagination-ajax.js'></script>
</html>
Paginated database results in a tabular view
This list.php file is to fetch and output the results to the UI. This PHP pagination request is called via AJAX by clicking the page links.
The below code prepares the MySQL query to fetch the data from the database. This query uses LIMIT to display the data with the per-page limit.
This code gets the total records’ count and per-page limit. It passes these two parameters the showPerpage() function to generate the PHP pagination HTML.
Added to the pagination parameters, this PHP code receives the search, sort request.
It prepares the WHERE and ORDER BY clause based on the search and sort request. These clauses are attached to the query to get the filtered result.
list.php
<?php
use Phppot\DataSource;
require_once "perpage.php";
require_once __DIR__ . "/lib/DataSource.php";
$dataSource = new DataSource();
$name = "";
$queryCondition = "";
$paramType = "";
$paramValue = array();
if (! empty($_POST["filter_value"])) {
$name = $_POST["filter_value"];
$queryCondition .= " WHERE name LIKE ?";
$paramType .= 's';
$paramValue[] = $_POST["filter_value"] . '%';
}
$orderBy = "name";
$order = "asc";
if (! empty($_POST["order_type"])) {
$order = $_POST["order_type"];
}
$nextOrder = "asc";
if ($order == "asc") {
$nextOrder = "desc";
}
$query = "SELECT * from tbl_member " . $queryCondition . " ORDER BY " . $orderBy . " " . $order . " ";
$href = 'index.php';
$perPage = 5;
$page = 1;
if (isset($_POST['pagination_offset'])) {
$page = $_POST['pagination_offset'];
}
$start = ($page - 1) * $perPage;
if ($start < 0)
$start = 0;
$queryWithLimit = $query . " limit " . $start . "," . $perPage;
$resultpage = $dataSource->select($queryWithLimit, $paramType, $paramValue);
if (! empty($resultpage)) {
$count = $dataSource->getRecordCount($query, $paramType, $paramValue);
$resultpage["perpage"] = showperpage($count, $perPage, $href);
}
?>
<?php
if (! empty($resultpage)) {
foreach ($resultpage as $k => $v) {
if (is_numeric($k)) {
?>
<?php
$date = $resultpage[$k]["date"];
$newDate = date("d-m-Y", strtotime($date));
?>
<tr>
<td class="column1"><?php echo $resultpage[$k]["name"]; ?></td>
<td class="column2"><?php echo $newDate ?></td>
<td class="column3 align-right">$ <?php echo $resultpage[$k]["subscription_amount"]; ?></td>
</tr>
<?php
}
}
}
if (isset($resultpage["perpage"])) {
?>
<tr>
<td colspan="5" class="align-center"> <?php echo $resultpage["perpage"]; ?></td>
</tr>
<?php } ?>
External handlers to prepare PHP pagination controls
It uses a constant to split the database results into pages. This code uses the following two functions.
- perpage() – prepares the HTML for the pagination UI links.
- showperpage() – returns pagination HTML response.
The perpage() function adds the onClick attribute to the PHP pagination links. It calls the AJAX code by sending the pagination query offset.
It uses the offset, per-page count and the total number of records to calculate the number of pages. It runs a loop through the number of pages to display the pagination links.
It adds HTML attributes to highlight the current page among the page links.
perpage.php
<?php
function perpage($count, $per_page = '5',$href) {
$output = '';
$paging_id = "link_perpage_box";
if(!isset($_POST["pagination_offset"])) $_POST["pagination_offset"] = 1;
if($per_page != 0)
$pages = ceil($count/$per_page);
if($pages>1) {
if(($_POST["pagination_offset"]-3)>0) {
if($_POST["pagination_offset"] == 1)
$output = $output . '<span id=1 class="current-page">1</span>';
else
$output = $output . '<input type="hidden" name="pagination_offset" value="1" /><input type="button" class="perpage-link" value="1" onClick="listViaAJAX()" />';
}
if(($_POST["pagination_offset"]-3)>1) {
$output = $output . '...';
}
for($i=($_POST["pagination_offset"]-2); $i<=($_POST["pagination_offset"]+2); $i++)
{
if($i<1) continue;
if($i>$pages) break;
if($_POST["pagination_offset"] == $i)
$output = $output . '<span id='.$i.' class="current-page" >'.$i.'</span>';
else
$output = $output . '<input type="hidden" name="pagination_offset" value="' . $i . '" /><input type="button" class="perpage-link" value="' . $i . '" onClick="listViaAJAX()" />';
}
if(($pages-($_POST["pagination_offset"]+2))>1) {
$output = $output . '...';
}
if(($pages-($_POST["pagination_offset"]+2))>0) {
if($_POST["pagination_offset"] == $pages)
$output = $output . '<span id=' . ($pages) .' class="current-page">' . ($pages) .'</span>';
else
$output = $output . '<input type="hidden" name="pagination_offset" value="' . $pages . '" /><input type="button" class="perpage-link" value="' . $pages . '" onClick="listViaAJAX()" />';
}
}
return $output;
}
function showperpage($count, $per_page = 5, $href)
{
$perpage = perpage($count, $per_page,$href);
return $perpage;
}
?>
jQuery AJAX script to call paginated list from PHP
This jQuery AJAX script calls the PHP pagination code on the server-side.
It calls the PHP endpoint index.php by sending the form data. It serializes and posts the following data to the PHP.
- Page offset. Default is 0.
- Search keyword.
- Sorting order. Default is ascending order(ASC).
The sortlist() function toggles the sorting order by clicking the corresponding column name.
pagination-ajax.js
function listViaAJAX() {
$.ajax({
method: "POST",
url: "list.php",
data: $("#frmSearch").serialize(),
}).done(function( response ) {
$("#table-body").html(response);
});
}
function sortList() {
if($("#order-type").val() == 'asc') {
$("#order-type").val('desc');
$(".sortable img").attr("src", "images/sort-up.svg");
} else {
$("#order-type").val('asc');
$(".sortable img").attr("src", "images/sort-down.svg");
}
listViaAJAX();
}
Database script
Download the below database script into your environment. It shows the database schema with the table structure.
It also has the sample data in the target database table. It supports to start seeing the PHP pagination code result with search.
sql/database.sql
--
-- Database: `blog_eg`
--
-- --------------------------------------------------------
--
-- Table structure for table `tbl_member`
--
CREATE TABLE `tbl_member` (
`id` int(255) NOT NULL,
`name` varchar(256) NOT NULL,
`date` date NOT NULL,
`subscription_amount` varchar(256) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
--
-- Dumping data for table `tbl_member`
--
INSERT INTO `tbl_member` (`id`, `name`, `date`, `subscription_amount`) VALUES
(1, 'Jennifer d\' Sousa ', '2022-01-20', '10'),
(2, 'Tom Adomian', '2021-12-25', '10'),
(3, 'Vincent Thomas', '2021-12-24', '100'),
(4, 'Lara Ricky', '2021-12-25', '100'),
(5, 'Kevin Fernandes', '2021-12-17', '100'),
(6, 'Leena Christy', '2021-12-16', '40'),
(7, 'Christian Benchamin', '2021-12-19', '60'),
(8, 'Abraham Maslow', '2021-12-17', '175'),
(9, 'Helan Immaculate', '2021-12-16', '190'),
(10, 'Jane Jancy', '2021-12-19', '30');
--
-- Indexes for dumped tables
--
--
-- Indexes for table `tbl_member`
--
ALTER TABLE `tbl_member`
ADD PRIMARY KEY (`id`);
--
-- AUTO_INCREMENT for dumped tables
--
--
-- AUTO_INCREMENT for table `tbl_member`
--
ALTER TABLE `tbl_member`
MODIFY `id` int(255) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=24;
PHP pagination output
The following screenshot displays the PHP pagination code. It shows the tabular results with pagination links and a search panel.
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.
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.