Ajax Pagination enhances the user experience when viewing paginated material on a website page. Using jQuery and Ajax, the navigation file is recorded from the server without requiring a page refresh. This article will show you how to use the column sorting feature in PHP to improve the Ajax pagination capability.
The majority of the time, the records are extracted from the data and displayed in an HTML table. Along with pagination, the row filtering function is highly beneficial for improving the user experience of the content list table. The header rows of the HTML table will be editable, allowing the user to sort the entries in lowest to highest. Ajax pagination with pillar sort enables users to easily access a huge data list via pagination links and sort the data list by selecting columns in lowest to highest.
The following functionality will be implemented in the Ajax Pagination using Column Arranging module.
- Using PHP, get current data from the Database server and list them in an HTML table.
- By using Ajax Pagination module, add pagination hyperlinks to the information list table.
- Make the header columns of an HTML table accessible for information filtering.
- Enables the customers to paginate and arrange data without having to reload the page.
Take a look at the file structure before beginning to construct Ajax pagination with sorting by column in PHP.
ajax_pagination_with_search_filter/ ├── dbConfig.php ├── index.php ├── getData.php ├── Pagination.class.php ├── js/ │ └── jquery.min.js └── css/ ├── bootstrap.min.css └── style.css
To hold dynamic information in the database, a table is necessary. In the MySQL database, the following SQL creates a people database with some fundamental columns.
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 customized PHP module aids in the development of pagination links through Ajax. To combine ajax pagination using column filtering in PHP, use the following configuration parameters.
- totalRows – The total number of rows.
- perPage — Keep track of the numbers that will be displayed on each page.
- currentPage – The page number that is currently in use.
- contentDiv – The ID of the HTML element in which the Ajax response information will be displayed.
- link func – The name of the function that performs the sort by column name function.
/**
* 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 = '
Showing : ' . $this->totalRows.'
'; 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 '.$this->baseURL.'?'.$count.'"'.$this->anchorClass.'>'.$text.''; $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 ". $this->anchorClass . " ". $linkClick .">". $text .''; } }// 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); }
<link href="css/bootstrap.min.css" rel="stylesheet">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.6.0/jquery.min.js">script>
- Get the name of the column and the sort order value.
- Ajax is used to deliver the sort order to the server-side script (getData.php).
- Set the dataContainer HTML element's data and change the data source with the 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 code below manages the table header page's click action and calls the columnSorting() method.
$(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:
- To create the Web pages table, sortable by columns.
- Add the sortable class to the table
<table class="sortable">
...
table>
- Add sorting
class and coltype
(value should be field name) & colorder
attributes to header column.
<th scope="col" class="sorting" coltype="id" colorder="">IDth>
// 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">
<div class="loading-overlay"><div class="overlay-content">Loading...div>div>
<div id="dataContainer">
<table class="table table-striped sortable">
<thead>
<tr>
<th scope="col" class="sorting" coltype="id" colorder="">#IDth>
<th scope="col" class="sorting" coltype="first_name" colorder="">First Nameth>
<th scope="col" class="sorting" coltype="last_name" colorder="">Last Nameth>
<th scope="col" class="sorting" coltype="email" colorder="">Emailth>
<th scope="col" class="sorting" coltype="country" colorder="">Countryth>
<th scope="col">Statusth>
tr>
thead>
<tbody>
if($query->num_rows > 0){
while($row = $query->fetch_assoc()){
?>
<tr>
<th scope="row">echo $row["id"]; ?>th>
<td>echo $row["first_name"]; ?>td>
<td>echo $row["last_name"]; ?>td>
<td>echo $row["email"]; ?>td>
<td>echo $row["country"]; ?>td>
<td>echo ($row["status"] == 1)?'Active':'Inactive'; ?>td>
tr>
}
}else{
echo '
No records found...'; } ?> tbody> table> echo $pagination->createLinks(); ?> div> div>
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"); ?>
© ThemesGiant Copyright @2015-2022 | All rights reserved.