PHP ,Mysql Pagination with jQuery Ajax

Pagination is very important part in any web project .Because all large records of items can't load in a single page . We require pagination to show these items in multiple pages . Well Jquery,Ajax pagination enhance user experience on your website . So, today i am writing tutorials on PHP pagination with Jquery,Ajax .

We will use PHP to fetch post from Mysql database with pagination . Jquery,Ajax will do the work for loading records according to pagination click without page reloading . We should have below files before starting any work .

 pagination.php
 config.php
 get_data.php
 index.php
 jquery.js
 style.css 

Database Creation

We have to create a table 'posts' to store records , Find below sql code to create 'posts' database table .

 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 have created a paginator class to show pagination . Find below some basic configuration in this class that you can customize as per your requirements . baseURL – baseURL holds the URL to send Ajax request . 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

 <?php
// Pagination  settings
$pagConfig = array('baseURL'=>'get_data.php', 'totalRows'=>$rowCount, 'perPage'=>$limit, 'contentDiv'=>'posts_content');
// Initialize pagination class
$pagination =  new Pagination($pagConfig);
?> <!-- Display pagination links --> <?php echo $pagination->createLinks(); ?> 

config.php File

For Databse connection and DB select

 <?php
$dbHost = "localhost";
$dbUsername = "root";
$dbPassword = "";
$dbName = "unitedwebsoft";
// 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

Full script of index.php file have mentioned separately below. Jquery: 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.js">  // 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();
});  

PHP & HTML :

With the pagination links, will will display limited no. of records . To display the pagination links, you have 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('config.php');
 $limit = 10;
 //Get the total number of rows
 $queryNum = $db->query("SELECT COUNT(*) as postNum FROM posts");
 $resultNum = $queryNum->fetch_assoc();
 $rowCount = $resultNum;
 //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;
  ?> <div class="list_item"><a href="javascript:void(0);"><h2 id=""><?php echo $row; ?></h2></a></div> <?php } ?> </div> <?php echo $pagination->createLinks(); ?> <?php } ?> </div></div> 

get_data.php File

This page called by Ajax. Here We’ll get the page number of the pagination link and fetch the respective posts data.

 <?php
if(isset($_POST)){
 //Include Pagination class file
 include('pagination.php');
 //Include database configuration file
 include('config.php');
 $start = !empty($_POST:0;
 $limit = 10;
 //get number of rows
 $queryNum = $db->query("SELECT COUNT(*) as postNum FROM posts");
 $resultNum = $queryNum->fetch_assoc();
 $rowCount = $resultNum;
 //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;
  ?> <div class="list_item"><a href="javascript:void(0);"><h2 id=""><?php echo $row; ?></h2></a></div> <?php } ?> </div> <?php echo $pagination->createLinks(); ?> <?php }
}
?> 

CSS (style.css)

Below css used for pagination links layout , you can customize it as per your website designs.

  div.pagination {
 font-family:Arial, Helvetica, sans-serif;
 padding:20px;
 margin:7px;
}
div.pagination a {
 margin: 2px;
 padding: 0.5em 0.64em 0.43em 0.64em;
 background-color: #ee4e4e;
 text-decoration: none;
 color: #fff;
}
div.pagination a:hover, div.pagination a:active {
 padding: 0.5em 0.64em 0.43em 0.64em;
 margin: 2px;
 background-color: #de1818;
 color: #fff;
}
div.pagination span.current {
 padding: 0.5em 0.64em 0.43em 0.64em;
 margin: 2px;
 background-color: #f6efcc;
 color: #6d643c;
}
div.pagination span.disabled {
 display:none;
} 

Finally you have successfully integrated PHP Pagination with Jquery and Ajax . If you looking more PHP tutorials visit here. For advance level PHP Project based training visit here .