close
Ajax Pagination improves user experience while accessing paginated data on the web page. The paging data is loaded from the server-side without page refresh using jQuery and Ajax. Our previous Ajax pagination with PHP tutorial guided you to add pagination functionality to the data list using Query, Ajax, PHP, and MySQL. This tutorial will help you to enhance the Ajax pagination functionality with the column sorting feature in PHP.
Mostly, the records are fetched from the database and listed in an HTML table. The column sorting feature is very useful to improve the user experience of the data list table along with pagination. HTML table header columns will be clickable so that the user can sort the records by ascending or descending order. Ajax pagination with column sorting functionality allows the user to access a large data list quickly by pagination links and sort data list by clicking columns in ascending or descending order.
In the Ajax Pagination with Column Sorting script, we will implement the following functionality.
Before getting started to build Ajax pagination with sort by column in PHP, take a look at the file structure.
ajax_pagination_with_search_filter/ ├── dbConfig.php ├── index.php ├── getData.php ├── Pagination.class.php ├── js/ │ └── jquery.min.js └── css/ ├── bootstrap.min.css └── style.css
A table is required to store the dynamic data in the database. The following SQL creates an users table with some basic fields in the MySQL database.
CREATE TABLE `users` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`first_name` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
`last_name` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
`email` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
`country` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
`status` tinyint(1) NOT NULL DEFAULT 1 COMMENT '1=Active | 0=Inactive',
`created` datetime NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
This custom PHP library helps to handle the pagination links creation process with Ajax. You need to use the following configure options to integrate ajax pagination with column sorting in PHP.
totalRows – Total number of records.perPage – Record numbers to display on each page.currentPage – Current page number.contentDiv – HTML element ID where the Ajax response data will appear.link_func – Function name that handles sort by column name functionality.<?php
/**
* CodexWorld is a programming blog. Our mission is to provide the best online resources on programming and web development.
*
* This Pagination class helps to integrate ajax pagination in PHP.
*
* @class Pagination
* @author CodexWorld
* @link http://www.codexworld.com
* @contact http://www.codexworld.com/contact-us
* @version 1.0
*/
class Pagination{
var $baseURL = '';
var $totalRows = '';
var $perPage = 10;
var $numLinks = 3;
var $currentPage = 0;
var $firstLink = '‹ First';
var $nextLink = '>';
var $prevLink = '<';
var $lastLink = 'Last ›';
var $fullTagOpen = '<div class="pagination">';
var $fullTagClose = '</div>';
var $firstTagOpen = '';
var $firstTagClose = ' ';
var $lastTagOpen = ' ';
var $lastTagClose = '';
var $curTagOpen = ' <b>';
var $curTagClose = '</b>';
var $nextTagOpen = ' ';
var $nextTagClose = ' ';
var $prevTagOpen = ' ';
var $prevTagClose = '';
var $numTagOpen = ' ';
var $numTagClose = '';
var $anchorClass = '';
var $showCount = true;
var $currentOffset = 0;
var $contentDiv = '';
var $additionalParam= '';
var $link_func = '';
function __construct($params = array()){
if (count($params) > 0){
$this->initialize($params);
}
if ($this->anchorClass != ''){
$this->anchorClass = 'class="'.$this->anchorClass.'" ';
}
}
function initialize($params = array()){
if (count($params) > 0){
foreach ($params as $key => $val){
if (isset($this->$key)){
$this->$key = $val;
}
}
}
}
/**
* Generate the pagination links
*/
function createLinks(){
// If total number of rows is zero, do not need to continue
if ($this->totalRows == 0 OR $this->perPage == 0){
return '';
}
// Calculate the total number of pages
$numPages = ceil($this->totalRows / $this->perPage);
// Is there only one page? will not need to continue
if ($numPages == 1){
if ($this->showCount){
$info = '<p>Showing : ' . $this->totalRows.'</p>';
return $info;
}else{
return '';
}
}
// Determine the current page
if ( ! is_numeric($this->currentPage)){
$this->currentPage = 0;
}
// Links content string variable
$output = '';
// Showing links notification
if ($this->showCount){
$currentOffset = $this->currentPage;
$info = 'Showing ' . ( $currentOffset + 1 ) . ' to ' ;
if( ($currentOffset + $this->perPage) < $this->totalRows)
$info .= $currentOffset + $this->perPage;
else
$info .= $this->totalRows;
$info .= ' of ' . $this->totalRows . ' | ';
$output .= $info;
}
$this->numLinks = (int)$this->numLinks;
// Is the page number beyond the result range? the last page will show
if ($this->currentPage > $this->totalRows){
$this->currentPage = ($numPages - 1) * $this->perPage;
}
$uriPageNum = $this->currentPage;
$this->currentPage = floor(($this->currentPage/$this->perPage) + 1);
// Calculate the start and end numbers.
$start = (($this->currentPage - $this->numLinks) > 0) ? $this->currentPage - ($this->numLinks - 1) : 1;
$end = (($this->currentPage + $this->numLinks) < $numPages) ? $this->currentPage + $this->numLinks : $numPages;
// Render the "First" link
if ($this->currentPage > $this->numLinks){
$output .= $this->firstTagOpen
. $this->getAJAXlink( '' , $this->firstLink)
. $this->firstTagClose;
}
// Render the "previous" link
if ($this->currentPage != 1){
$i = $uriPageNum - $this->perPage;
if ($i == 0) $i = '';
$output .= $this->prevTagOpen
. $this->getAJAXlink( $i, $this->prevLink )
. $this->prevTagClose;
}
// Write the digit links
for ($loop = $start -1; $loop <= $end; $loop++){
$i = ($loop * $this->perPage) - $this->perPage;
if ($i >= 0){
if ($this->currentPage == $loop){
$output .= $this->curTagOpen.$loop.$this->curTagClose;
}else{
$n = ($i == 0) ? '' : $i;
$output .= $this->numTagOpen
. $this->getAJAXlink( $n, $loop )
. $this->numTagClose;
}
}
}
// Render the "next" link
if ($this->currentPage < $numPages){
$output .= $this->nextTagOpen
. $this->getAJAXlink( $this->currentPage * $this->perPage , $this->nextLink )
. $this->nextTagClose;
}
// Render the "Last" link
if (($this->currentPage + $this->numLinks) < $numPages){
$i = (($numPages * $this->perPage) - $this->perPage);
$output .= $this->lastTagOpen . $this->getAJAXlink( $i, $this->lastLink ) . $this->lastTagClose;
}
// Remove double slashes
$output = preg_replace("#([^:])//+#", "\\1/", $output);
// Add the wrapper HTML if exists
$output = $this->fullTagOpen.$output.$this->fullTagClose;
return $output;
}
function getAJAXlink( $count, $text) {
if($this->link_func == '' && $this->contentDiv == '')
return '<a href="'.$this->baseURL.'?'.$count.'"'.$this->anchorClass.'>'.$text.'</a>';
$pageCount = $count?$count:0;
if(!empty($this->link_func)){
$linkClick = 'onclick="'.$this->link_func.'('.$pageCount.')"';
}else{
$this->additionalParam = "{'page' : $pageCount}";
$linkClick = "onclick=\"$.post('". $this->baseURL."', ". $this->additionalParam .", function(data){
$('#". $this->contentDiv . "').html(data); }); return false;\"";
}
return "<a href=\"javascript:void(0);\" " . $this->anchorClass . "
". $linkClick .">". $text .'</a>';
}
}
?>
The dbConfig.php file is used to connect and select the database. Specify the database host ($dbHost), username ($dbUsername), password ($dbPassword), and name ($dbName) as per your MySQL database server credentials.
<?php
// Database configuration
$dbHost = "localhost";
$dbUsername = "root";
$dbPassword = "root";
$dbName = "codexworld";
// Create database connection
$db = new mysqli($dbHost, $dbUsername, $dbPassword, $dbName);
// Check connection
if ($db->connect_error) {
die("Connection failed: " . $db->connect_error);
}
Initially, the HTML table will contain records from the MySQL database and pagination links are placed under the table. Additionally, the sort by click on column feature is attached to the table header.
Bootstrap Library:
We will use the Bootstrap library for styling the data list table. So, include the Bootstrap CSS library file to define the styles for the HTML table and header columns.
<link href="css/bootstrap.min.css" rel="stylesheet">
Note that: If you don’t want to use Bootstrap for table styling, it can be omitted from include.
jQuery Library:
Include the jQuery library, it is required to handle Ajax requests for pagination and data sorting without page refresh.
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.6.0/jquery.min.js"></script>
Sort by Columns:
The columnSorting() is a custom JavaScript function that handles the column sorting functionality using jQuery and Ajax.
getData.php) via Ajax.function columnSorting(page_num){
page_num = page_num?page_num:0;
let coltype='',colorder='',classAdd='',classRemove='';
$( "th.sorting" ).each(function() {
if($(this).attr('colorder') != ''){
coltype = $(this).attr('coltype');
colorder = $(this).attr('colorder');
if(colorder == 'asc'){
classAdd = 'asc';
classRemove = 'desc';
}else{
classAdd = 'desc';
classRemove = 'asc';
}
}
});
$.ajax({
type: 'POST',
url: 'getData.php',
data:'page='+page_num+'&coltype='+coltype+'&colorder='+colorder,
beforeSend: function () {
$('.loading-overlay').show();
},
success: function (html) {
$('#dataContainer').html(html);
if(coltype != '' && colorder != ''){
$( "th.sorting" ).each(function() {
if($(this).attr('coltype') == coltype){
$(this).attr("colorder", colorder);
$(this).removeClass(classRemove);
$(this).addClass(classAdd);
}
});
}
$('.loading-overlay').fadeOut("slow");
}
});
}
The following code handles the click event on the table header column and triggers the columnSorting() function.
$(function(){
$(document).on("click", "th.sorting", function(){
let current_colorder = $(this).attr('colorder');
$('th.sorting').attr('colorder', '');
$('th.sorting').removeClass('asc');
$('th.sorting').removeClass('desc');
if(current_colorder == 'asc'){
$(this).attr("colorder", "desc");
$(this).removeClass("asc");
$(this).addClass("desc");
}else{
$(this).attr("colorder", "asc");
$(this).removeClass("desc");
$(this).addClass("asc");
}
columnSorting();
});
});
Attach Sorting to Column and Add Pagination:
A limited number of records are listed in an HTML table with pagination links. The sort feature is attached by adding up/down arrows to the table header columns.
createLinks() function of the Pagination class to generate pagination links and display them under the data list table.sortable class to the table.
<table class="sortable">
...
</table>
sorting class and coltype (value should be field name) & colorder attributes to header column.
<th scope="col" class="sorting" coltype="id" colorder="">ID</th>
<?php
// Include pagination library file
include_once 'Pagination.class.php';
// Include database configuration file
require_once 'dbConfig.php';
// Set some useful configuration
$limit = 5;
// Count of all records
$query = $db->query("SELECT COUNT(*) as rowNum FROM users");
$result = $query->fetch_assoc();
$rowCount= $result['rowNum'];
// Initialize pagination class
$pagConfig = array(
'totalRows' => $rowCount,
'perPage' => $limit,
'contentDiv' => 'dataContainer',
'link_func' => 'columnSorting'
);
$pagination = new Pagination($pagConfig);
// Fetch records based on the limit
$query = $db->query("SELECT * FROM users ORDER BY id DESC LIMIT $limit");
?>
<div class="datalist-wrapper">
<!-- Loading overlay -->
<div class="loading-overlay"><div class="overlay-content">Loading...</div></div>
<!-- Data list container -->
<div id="dataContainer">
<table class="table table-striped sortable">
<thead>
<tr>
<th scope="col" class="sorting" coltype="id" colorder="">#ID</th>
<th scope="col" class="sorting" coltype="first_name" colorder="">First Name</th>
<th scope="col" class="sorting" coltype="last_name" colorder="">Last Name</th>
<th scope="col" class="sorting" coltype="email" colorder="">Email</th>
<th scope="col" class="sorting" coltype="country" colorder="">Country</th>
<th scope="col">Status</th>
</tr>
</thead>
<tbody>
<?php
if($query->num_rows > 0){
while($row = $query->fetch_assoc()){
?>
<tr>
<th scope="row"><?php echo $row["id"]; ?></th>
<td><?php echo $row["first_name"]; ?></td>
<td><?php echo $row["last_name"]; ?></td>
<td><?php echo $row["email"]; ?></td>
<td><?php echo $row["country"]; ?></td>
<td><?php echo ($row["status"] == 1)?'Active':'Inactive'; ?></td>
</tr>
<?php
}
}else{
echo '<tr><td colspan="6">No records found...</td></tr>';
}
?>
</tbody>
</table>
<!-- Display pagination links -->
<?php echo $pagination->createLinks(); ?>
</div>
</div>
The getData.php file is loaded by Ajax request (called by columnSorting() function) to retrieve the paginated records from the database.
<?php
if(isset($_POST['page'])){
// Include pagination library file
include_once 'Pagination.class.php';
// Include database configuration file
require_once 'dbConfig.php';
// Set some useful configuration
$offset = !empty($_POST['page'])?$_POST['page']:0;
$limit = 5;
// Set conditions for column sorting
$sortSQL = '';
if(!empty($_POST['coltype']) && !empty($_POST['colorder'])){
$coltype = $_POST['coltype'];
$colorder = $_POST['colorder'];
$sortSQL = " ORDER BY $coltype $colorder";
}
// Count of all records
$query = $db->query("SELECT COUNT(*) as rowNum FROM users");
$result = $query->fetch_assoc();
$rowCount= $result['rowNum'];
// Initialize pagination class
$pagConfig = array(
'totalRows' => $rowCount,
'perPage' => $limit,
'currentPage' => $offset,
'contentDiv' => 'dataContainer',
'link_func' => 'columnSorting'
);
$pagination = new Pagination($pagConfig);
// Fetch records based on the offset and limit
$query = $db->query("SELECT * FROM users $sortSQL LIMIT $offset,$limit");
?>
<!-- Data list container -->
<table class="table table-striped sortable">
<thead>
<tr>
<th scope="col" class="sorting" coltype="id" colorder="">#ID</th>
<th scope="col" class="sorting" coltype="first_name" colorder="">First Name</th>
<th scope="col" class="sorting" coltype="last_name" colorder="">Last Name</th>
<th scope="col" class="sorting" coltype="email" colorder="">Email</th>
<th scope="col" class="sorting" coltype="country" colorder="">Country</th>
<th scope="col">Status</th>
</tr>
</thead>
<tbody>
<?php
if($query->num_rows > 0){
while($row = $query->fetch_assoc()){
?>
<tr>
<th scope="row"><?php echo $row["id"]; ?></th>
<td><?php echo $row["first_name"]; ?></td>
<td><?php echo $row["last_name"]; ?></td>
<td><?php echo $row["email"]; ?></td>
<td><?php echo $row["country"]; ?></td>
<td><?php echo ($row["status"] == 1)?'Active':'Inactive'; ?></td>
</tr>
<?php
}
}else{
echo '<tr><td colspan="6">No records found...</td></tr>';
}
?>
</tbody>
</table>
<!-- Display pagination links -->
<?php echo $pagination->createLinks(); ?>
<?php
}
?>
Ajax Pagination with Search and Filter in PHP
If you want to add the sort functionality to the data list table, column sorting is the best and user-friendly way to sort list by columns. This example script helps you to add ajax pagination functionality with sort table by column in PHP. By adding the sort feature to header columns in the HTML table, the column name will be clickable and the user can toggle the data list to sort by ascending or descending order (lowest or highest value).
Do you want to get implementation help, or enhance the functionality of this script? Click here to Submit Service Request
