Pagination with jQuery Ajax PHP and MySQL


In the web project, pagination is a very important part where huge numbers of records are listed from the database. In that case, Ajax pagination is a preferable way because it will help to improve the User Interface of your website. This tutorial will show how can you implement the Ajax pagination in PHP using jQuery and MySQL. We’ve developed the simple and powerful script to create pagination with jQuery, Ajax, PHP, and MySQL.

pagination-with-jquery-ajax-php-mysql-by-codexworld

Using PHP, we’ll display the posts from the MySQL database with the pagination links. Through this pagination links, you can get the database records except the displayed records. jQuery and Ajax will help to get the records from the database depending on pagination links without reloading the page.

Before starting Ajax Pagination in PHP, take a look at the files structure.

  • Pagination.php
  • dbConfig.php
  • getData.php
  • index.php
  • jquery.min.js
  • style.css

Database Creation

We have a posts database table and we’ll implement the pagination with this posts records. The posts table SQL would be like the below.

CREATE TABLE `posts` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `title` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
 `content` text COLLATE utf8_unicode_ci NOT NULL,
 `created` datetime NOT NULL,
 `modified` datetime NOT NULL,
 `status` tinyint(1) NOT NULL DEFAULT '1' COMMENT '1=Active, 0=Inactive',
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Pagination Class

We’ve created a Pagination class for generating the pagination links. You can pass the links configuration option within Pagination class. The commonly used configuration options are the below.

  • baseURL – baseURL holds the URL for send Ajax request and we’ll get the posts records of the respective pagination link by this URL.
  • totalRows – Total number of records.
  • perPage – How many records you want to display on per page.
  • contentDiv – Give the ID of the element where the pagination data would be displayed.

Uses of the Pagination class would like the below.

<?php
// Pagination links configuration
$pagConfig = array('baseURL'=>'getData.php''totalRows'=>$rowCount'perPage'=>$limit'contentDiv'=>'posts_content');
// Initialize pagination class
$pagination =  new Pagination($pagConfig);
?>

<!-- Display pagination links -->
<?php echo $pagination->createLinks(); ?>

dbConfig.php File

This file helps to connect and select the database.

<?php
$dbHost 
"localhost";
$dbUsername "root";
$dbPassword "";
$dbName "codexworld";

// Create database connection
$db = new mysqli($dbHost$dbUsername$dbPassword$dbName);

// Check connection
if ($db->connect_error) {
    die(
"Connection failed: " $db->connect_error);
}
?>

index.php File

This file contains some JavaScript, PHP, and HTML code. Full script of index.php file is mentioned separately below.

JavaScript:
Include the jQuery library to working with jQuery and Ajax. Also, some jQuery would require for show and hide loading overlay at the time of Ajax request.

<script src="jquery.min.js"></script>
<script type="text/javascript">
// Show loading overlay when ajax request starts
$( document ).ajaxStart(function() {
    $('.loading-overlay').show();
});
// Hide loading overlay when ajax request completes
$( document ).ajaxStop(function() {
    $('.loading-overlay').hide();
});
</script>

PHP & HTML:
At first, we’ll display the limited number of posts data with the pagination links. To display the pagination links, you should need to create an object of the Pagination class. Also, you can pass the configuration of the pagination links within Pagination class. Call the createLinks() function of the Pagination class to displaying the pagination links.

<div class="post-wrapper">
    <div class="loading-overlay"><div class="overlay-content">Loading.....</div></div>
    <div id="posts_content">
    <?php
    
//Include Pagination class file
    
include('Pagination.php');
    
    
//Include database configuration file
    
include('dbConfig.php');
    
    
$limit 10;
    
    
//Get the total number of rows
    
$queryNum $db->query("SELECT COUNT(*) as postNum FROM posts");
    
$resultNum $queryNum->fetch_assoc();
    
$rowCount $resultNum['postNum'];
    
    
//Initialize Pagination class and create object
    
$pagConfig = array('baseURL'=>'getData.php''totalRows'=>$rowCount'perPage'=>$limit'contentDiv'=>'posts_content');
    
$pagination =  new Pagination($pagConfig);
    
    
//Get rows
    
$query $db->query("SELECT * FROM posts RDER BY id DESC LIMIT $limit");
    
    if(
$rowCount 0){ ?>
        <div class="posts_list">
        <?php
            
while($row $query->fetch_assoc()){ 
                
$postID $row['id'];
        
?>
            <div class="list_item"><a href="javascript:void(0);"><h2><?php echo $row["title"]; ?></h2></a></div>
        <?php ?>
        </div>
        <?php echo $pagination->createLinks(); ?>
    <?php ?>
    </div>
</div>

getData.php File

This page is requested by Ajax. We’ll get the page number of the pagination link and render the respective posts data.

<?php
if(isset($_POST['page'])){
    
//Include Pagination class file
    
include('Pagination.php');
    
    
//Include database configuration file
    
include('dbConfig.php');
    
    
$start = !empty($_POST['page'])?$_POST['page']:0;
    
$limit 10;
    
    
//get number of rows
    
$queryNum $db->query("SELECT COUNT(*) as postNum FROM posts");
    
$resultNum $queryNum->fetch_assoc();
    
$rowCount $resultNum['postNum'];
    
    
//initialize Pagination class
    
$pagConfig = array('baseURL'=>'getData.php''totalRows'=>$rowCount'currentPage'=>$start'perPage'=>$limit'contentDiv'=>'posts_content');
    
$pagination =  new Pagination($pagConfig);
    
    
//get rows
    
$query $db->query("SELECT * FROM posts ORDER BY id DESC LIMIT $start,$limit");
    
    if($rowCount 0){ ?>
        <div class="posts_list">
        <?php
            while($row $query->fetch_assoc()){ 
                
$postID $row['id'];
        
?>
            <div class="list_item"><a href="javascript:void(0);"><h2><?php echo $row["title"]; ?></h2></a></div>
        <?php ?>
        </div>
        <?php echo $pagination->createLinks(); ?>
<?php 
}
}
?>

Conclusion

For making the pagination look better, we’ve written some CSS and you can find all the CSS in style.css file. Checkout the Ajax Pagination demo and download all file from the Download link. Also you can checkout the Youtube, Twitter and Facebook like pagination from here – Load more data using jQuery Ajax and PHP from database

Leave a reply

Connect With CodexWorld