Ajax Pagination with Column Sorting in PHP
loader
Ajax Pagination with Column Sorting in PHP
Jan 09, 2022
Laravel
By: Sarck Solution

Ajax Pagination with Column Sorting in PHP

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.

  • Fetch the dynamic records from the MySQL database and listed in an HTML table using PHP.
  • Add pagination links to the data list table using the Ajax Pagination library.
  • Make HTML table header columns clickable for data sorting.
  • Allow the user to paginate and sort records without page reload.

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

Create Database Table

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;

Pagination Library

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      '&lsaquo; First';
    var 
$nextLink       '&gt;';
    var 
$prevLink       '&lt;';
    var 
$lastLink       'Last &rsaquo;';
    var 
$fullTagOpen    '<div class="pagination">';
    var 
$fullTagClose   '</div>';
    var 
$firstTagOpen   '';
    var 
$firstTagClose  '&nbsp;';
    var 
$lastTagOpen    '&nbsp;';
    var 
$lastTagClose   '';
    var 
$curTagOpen     '&nbsp;<b>';
    var 
$curTagClose    '</b>';
    var 
$nextTagOpen    '&nbsp;';
    var 
$nextTagClose   '&nbsp;';
    var 
$prevTagOpen    '&nbsp;';
    var 
$prevTagClose   '';
    var 
$numTagOpen     '&nbsp;';
    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 == 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 ) . ' 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>';
    }
}
?>

Database Configuration (dbConfig.php)

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);
}

Data List with Ajax Pagination and Column Sorting (index.php)

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.

  • Using pagination links the user will be able to get the paginated records from the database without page refresh using jQuery and Ajax.
  • The column sorting feature works in a way where data is listed in ascending order when clicking on the header column for the first time. Likewise, data are listed in descending order when clicking on the column second time.

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.

  • Get the column name and sort order value.
  • Send the sort request to the server-side script (getData.php) via Ajax.
  • Set the content to the dataContainer HTML element and update the data 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 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.

  • Use createLinks() function of the Pagination class to generate pagination links and display them under the data list table.
  • When the pagination link is clicked, the Ajax request is initiated and sent to the server-side script.
  • To make the HTML table sortable by columns,
    • Add 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="">ID</th>
  • The data list will 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>

Sort and Get Pagination Data (getData.php)

The getData.php file is loaded by Ajax request (called by columnSorting() function) to retrieve the paginated records from the database.

  • Fetch the data by sort order and paging limit & offset.
  • Render data list sort by column field value and add pagination links.
  • Return the HTML view of the data 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 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).

Tags:
Leave A Reply