Jquery Ajax Pagination enhances user experience while accessing paginated data on the web page. The paging data are loaded from the server-side without page reload using jQuery and Ajax. In this tutorial will help you to improve the Ajax pagination functionality with the column sorting attribute in PHP.
Generally, the data are fetched from the database and listed in an HTML table. The column sorting attribute is very helpful to enhance the user experience of the data list HTML table along with pagination. Table columns will be clickable so that the user can sort the records by ascending or descending view. Ajax pagination column sorting functionality approve the user to access a large record list rapidly by pagination links and sort the data list by clicking the columns in ascending or descending order.
In the jquery Ajax Pagination with Sorting, we will execute the following functionality.
- Get the dynamic records from the MySQL db and listed in an HTML table using PHP.
- Put pagination url to the data list table using the jquery Ajax Pagination.
- Make table header columns clickable for data sorting ascending and descending.
- Authorize the user to paginate and sort data without page refresh.
Before we start to build a query Ajax pagination with sorting by column in PHP, take a look at the file arrangement.
Create Database Table
A database table is needed to save the dynamic record in the database. Then we will create SQL user table with basic fields.
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;
Pagination Library
This custom PHP library helps us to deal with the pagination url creation process with jquery Ajax. You need to use the some configure options to merge jquery ajax pagination with HTML table column sorting in PHP.
- TotalRows – Total number of fetching data.
- perPage – data numbers to display on every page.
- currentPage – Current page number.
- contentDiv – HTML attribute ID where the jquery Ajax response record will appear.
- link_func – PHP Function name that handles sorting 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>';
}
}
?>
SQL DB Configuration file (dbConfig.php)
We are going to use dbConfig.php file to connect and select the DB. Define the DB host ($dbHost), username ($dbUsername), password ($dbPassword), and name ($dbName) as per your MySQL DB 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);
}
Record List with jquery Ajax Pagination and Column Sorting (index.php)
At first, the table will contain data from the MySQL DB and pagination url are located under the HTML table. Furthermore, the sort by click on table column aspect is attached to the HTML table header.
- Using pagination links the user will be able to get the paginated records from the DB without page reload using Ajax.
- The HTML column sorting feature works in a method where data are listed in ascending when clicking on the header column for the first time. Similarly, record is listed in descending when clicking on the table column second time.
Bootstrap Library:
We are gonna use the Bootstrap library table styling the record list table. So, add the Bootstrap 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 library for HTML table styling, it can be left out from include.
jQuery Library:
Include the jQuery library, it is needed to handle jquery Ajax requests for pagination and record sorting without page reload.
<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 data functionality using jQuery and Ajax.
- Get the name of column and sort order column value.
- Send the request for sorting to the server-side script (getData.php) via jquery Ajax.
- Set the data to the dataContainer HTML attributes and update the record list with sort order.
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 below code will work on 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();
});
});
add Sorting to Column and Add Pagination:
A simply number of data are listed in an HTML table with pagination url. The sort feature is attached by up/down arrows to the table header columns.
- Use createLinks() function of the Pagination class to generate pagination links and display them under the data list table.
- When the pagination url is clicked, the jquery Ajax request is started and sent to request the server-side script.
- To make the HTML table sortable by columns,
- Add sortable class to the table
<table class="sortable">
...
</table>
- Put sorting class and col-type (value should be field name) & colorder attributes to header column
<th scope="col" class="sorting" coltype="id" colorder="">ID</th>
- The record list will be sort by field value in ascending/descending order when the header column is clicked.
<?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>
Now, sort data and Get Pagination Data (getData.php)
The getData.php file is loaded by jquery Ajax request (called by columnSorting() function) to fetch the paginated data from the DB.
- Get the record by sort order and paging limit & offset.
- Render record list sort by column field value and add pagination url.
- Return the HTML table view of the record list with column sorting.
<?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
}
?>
Conclusion
If you want to add the sorting functionality to the html table list table, column sorting is the best and user-friendly way to sort list by columns. This example script helps you to add jquery ajax pagination functionality with sort HTML table by column in PHP using ajax. By adding the sort feature to table header columns in the HTML table, the column name will be clickable and the user can click up/down for data sorting (lowest or highest value).
0 Comments