PHP Pagination Code with Column Sorting using AJAX

Hello everyone, today we will learn how to make Ajax Pagination with Column Sorting in PHP and MySQL. I am using AJAX to fetch user records as page data is loaded from the database (server-side) without reloading or refreshing the whole web page using jQuery and Ajax. Scroll down to the page, so you can see the full source code available for this.

Next, Project structure your folder as follows.

src
├── dbConfig.php
├── getData.php
├── index.php
├── Pagination.php
└── style.css

Create Database and Tables

first, create a MySQL database called codeat21. And after that, create the table name called register.

Database information

Database Name – codeat21
Table Name – register

CREATE TABLE `register` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`email` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Database Configuration

First, Create a dbConfig.php file to connect the MySQL database are hostname, username, Password, and database name.

 dbConfig.php 
<?php
//DB details
$dbHost     = 'localhost';
$dbUsername = 'root';
$dbPassword = '';
$dbName     = 'codeat21';

//Create connection and select DB
$db = new mysqli($dbHost, $dbUsername, $dbPassword, $dbName);

if ($db->connect_error) {
    die("Unable to connect database: " . $db->connect_error);
} 
?>

 index.php 
<?php
include_once 'Pagination.php';
require_once 'dbConfig.php';
$limit = 7;
$query   = $db->query("SELECT COUNT(*) as rowNum FROM register");
$result  = $query->fetch_assoc();
$rowCount= $result['rowNum'];

$pagConfig = array(
    'totalRows' => $rowCount,
    'perPage' => $limit,
    'contentDiv' => 'dataContainer',
    'link_func' => 'columnSorting'
);
$pagination =  new Pagination($pagConfig);
$query = $db->query("SELECT * FROM register ORDER BY id DESC LIMIT $limit");
?>

<!DOCTYPE html>
<html lang="en-US">
<head>
<title>Ajax Pagination with Column Sorting using PHP by codeat21</title>
<meta charset="utf-8">
<link rel="stylesheet" href="style.css">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/css/bootstrap.min.css">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.6.0/jquery.min.js"></script>

<script>
// Custom function to handle sort by column operation
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");
        }
    });
}

$(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();
	});
});
</script>
</head>
<body>
<div class="container">
    <h1>PHP Pagination Code with Column Sorting using AJAX</h1>
	<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="name" colorder=""> Name</th>
                    <th scope="col" class="sorting" coltype="email" colorder="">Email</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["name"]; ?></td>
                        <td><?php echo $row["email"]; ?></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>
</div>
</body>
</html>

 getData.php 
<?php
if(isset($_POST['page'])){
    include_once 'Pagination.php';
    require_once 'dbConfig.php';
	$offset = !empty($_POST['page'])?$_POST['page']:0;
	$limit = 7;
    $sortSQL = '';
    if(!empty($_POST['coltype']) && !empty($_POST['colorder'])){
        $coltype = $_POST['coltype'];
        $colorder = $_POST['colorder'];
        $sortSQL = " ORDER BY $coltype $colorder";
    }
    $query   = $db->query("SELECT COUNT(*) as rowNum FROM register");
    $result  = $query->fetch_assoc();
    $rowCount= $result['rowNum'];
    $pagConfig = array(
        'totalRows' => $rowCount,
        'perPage' => $limit,
		'currentPage' => $offset,
		'contentDiv' => 'dataContainer',
		'link_func' => 'columnSorting'
    );
    $pagination =  new Pagination($pagConfig);
    $query = $db->query("SELECT * FROM register $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="name" colorder="">Name</th>
            <th scope="col" class="sorting" coltype="email" colorder="">Email</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["name"]; ?></td>
                <td><?php echo $row["email"]; ?></td>
            </tr>
        <?php
            }
        }else{
            echo '<tr><td colspan="6">No records found...</td></tr>';
        }
        ?>
    </tbody>
    </table>
    
    <!-- Display pagination links -->
    <?php echo $pagination->createLinks(); ?>
<?php
}
?>

 Pagination.php 
<?php
class Pagination{
	var $baseURL		= '';
	var $totalRows  	= '';
	var $perPage	 	= 10;
	var $numLinks		=  3;
	var $currentPage	=  0;
	var $firstLink   	= '‹ First';
	var $nextLink		= 'Next';
	var $prevLink		= 'Prev';
	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 ($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>';
	}
}
?>

Related keywords

DataTables AJAX Pagination with Search and Sort – PHP
Datatable Pagination, Sorting and Search – Server Side (PHP/MySQl) Using Ajax
Laravel Searching Column Sorting with Pagination using Ajax
PHP Pagination Code with Search using AJAX
MongoDB AJAX Pagination with Search and Sort using PHP
PHP ajax loading result and pagination with sorting
Datatable – Searching, Sorting and Pagination on server side
Ajax pagination with search and filter in PHP
How to Create Ajax Pagination and Filters With PHP
Instant Search with Pagination in PHP Mysql jQuery and Ajax
Pagination in PHP Function With Search Filter Example
Product Ajax Search Filter with Infinite Scroll more Using PHP
Display search result with pagination using PHP and MySQL
PHP CRUD with Search and Pagination
Create Simple Pagination Using PHP and MySQLi
How to Ajax Pagination with Column Sorting in PHP
Pagination, searching and sorting is not working in jquery
Php Pagination And Data Sorting
php gridview with pagination
How to sort html table columns using PHP jQuery and Ajax
Searching, Column Sorting with Pagination using Ajax in PHP
Mysql pagination get total count. You can connect to any PHP server
Server side (PHP) code
PHP pagination with sortable table on header click
PHP/MySQL: Ajax-Style Sorting and Pagination
Datatable ajax load data. This tag has been used for display
How to use DataTables for pagination in PHP?
Laravel Datatables Pagination with Search and Sort Example
Make Pagination using Ajax with Jquery, PHP and MySQL
Jquery Datatable Ajax Tutorial With Example Project
Laravel: Using Pagination, Sorting and Filtering
Paginating Real-Time Data with Cursor Based Pagination
AJAX Pagination with PHP Without Page Refresh
Ajax Pagination Using jQuery, PHP And MySQL
USA, US, America, United state, United kingdom, France, Canada, Itally

PHP Pagination

PHP is an open-source which means free to use. It is a server-side rendering language. The PHP Pagination is used to display the large data is split into our page limited data displayed. This code displays the first and last, prev, and next link buttons to navigate the section.

The first button is to navigate the initial page and the last button is the end of the last data to navigate. Next and Prev button to navigate the next page and the previous page navigate. All website developers are using a pagination code that is fast loading your website.

Download

Create Database and Tables

Database information

Database Configuration

Related keywords

PHP Pagination

Recent Posts

Books

The Joy of PHP: A Beginner’s Guide to Programming Interactive Web Applications with PHP and mySQL

React Native for Mobile Development: Harness the Power of React Native to Create Stunning iOS and Android Applications

React Native Cookbook: Recipes for solving common React Native development problems

Categories

Learn JavaScript Quickly: A Complete Beginner’s Guide to Learning JavaScript, Even If You’re New to Programming (Crash Course With Hands-On Project)

Modular Programming with PHP 7

Building RESTful Web Services with PHP 7: Lumen, Composer, API testing, Microservices, and more

PHP Tutorials: Programming with PHP and MySQL: Learn PHP 7 / 8 with MySQL databases for web Programming