Simple AJAX Pagination in jQuery, PHP PDO & MySQL

Hi! Happy New Year 2017!!!

This is my first post in the new year and let's see how to create simple ajax pagination in php pdo, mysql and jquery. Pagination script adds on for user experience by allowing users to skim through data across multiple pages instead of endless scrolling. But while implementing pagination in php certain things has to be taken care of.

For the matter, the process may get complicated with too many lines of code when not using external libraries or plugins. And refreshing the page every time user clicks on a page link is not going to look pleasant. You'll definitely need to be using AJAX.

So to simplify the process I'm going to use a jQuery plugin called Bootpag which is light-weight and handles ajax request at the background effortlessly. The plug-in rely on bootstrap framework.

Below I have shared a simple php pagination script. It fetch and display mysql record in a html table and split the records over multiple pages with pagination links at the bottom for navigation.

Read:

Create MySQL Database & Table:

Here are the sql commands to create required database and table. Run them in mysql interface to create db & table.

CREATE DATABASE IF NOT EXISTS `kms_demo`;

USE `kms_demo`;

CREATE TABLE IF NOT EXISTS `customers` (
  `id` int(8) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `email` varchar(60) NOT NULL,
  `city` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=14;

INSERT INTO `customers` (`id`, `name`, `email`, `city`) VALUES
(1, 'Jim Connor', 'jimconnor@yahoo.com', 'Las Vegas'),
(2, 'Taylor Fox', 'taylorfox@hotmail.com', 'San Francisco'),
(3, 'Daniel Greyson', 'danielgreyson@hotmail.com', 'New York'),
(4, 'Julia Brown', 'juliabrown@gmail.com', 'Los Angeles'),
(5, 'Rose Harris', 'roseharris@gmail.com', 'New York'),
(6, 'Lilly Whites', 'lillywhites@outlook.com', 'New York'),
(7, 'Jennifer Winters', 'jennie.winters001@gmail.com', 'Miami'),
(8, 'Michael Bruce', 'michaelbruce78@yahoo.com', 'Los Angeles'),
(9, 'John Alex', 'johnalex@example.com', 'Chicago'),
(10, 'Demi Milan', 'demimilan@gmail.com', 'Austin'),
(11, 'Austin Joseph', 'austin.joseph.boston@gmail.com', 'Boston'),
(12, 'Mark Higgins', 'mark.higgins21@yahoo.com', 'Houston'),
(13, 'Sean Kennedy', 'seankennedy.boss@outlook.com', 'Seattle');

Simple AJAX Pagination using jQuery & PHP PDO

First make sure you have all the below library files in place and then move on to coding part.

root
|__css
|    |___ bootstrap.min.css
|
|__js
    |___ bootstrap.min.js
    |___ jquery-1.10.2.js
    |___ jquery.bootpag.min.js

db_connect.php

This is the database configuration file. Here's where you define connection details to mysql and more importantly the number of records visible per page. Also I have used PDO library instead of mysqli to connect php to mysql database.

<?php
$hostname = "localhost";
$username = "username"; // change to yours
$password = "password"; // change to yours
$database = "kms_demo";
$row_limit = 5;

// connect to mysql
try {
    $pdo = new PDO("mysql:host=$hostname;dbname=$database", $username, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $err) {
    die("Error! " . $err->getMessage());
}
?>

The variable $row_limit defines number of records to be displayed per page. Just change this limit according to your need.

index.php

It is the main file to show records from mysql db as html table along with pagination links at the bottom. The database records are split into several pages and displayed one page at a time. In order to generate pagination links we need to calculate the number of pages. You have to just divide the total number of records by rows per page.

<?php
include_once("db_connect.php");

$stmt = $pdo->prepare("SELECT COUNT(*) FROM customers");
$stmt->execute();
$rows = $stmt->fetch();

// get total no. of pages
$total_pages = ceil($rows[0]/$row_limit);
?>

<!DOCTYPE html>
<html lang="en">
<head>
    <title>AJAX Pagination using PHP & MySQL</title>
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <link href="css/bootstrap.min.css" rel="stylesheet" type="text/css" />
    <style type="text/css">
    .panel-footer {
        padding: 0;
        background: none;
    }
    </style>
</head>
<body>
<br/>
<div class="container">
    <div class="panel panel-default">
        <div class="panel-heading text-center"><h3>jQuery PHP Pagination Demo</h3></div>

        <table class="table table-bordered table-hover">
            <thead>
                <tr>
                    <th>#</th>
                    <th>Name</th>
                    <th>Email ID</th>
                    <th>Location</th>
                </tr>
            </thead>
            <tbody id="pg-results">
            </tbody>
        </table>
        <div class="panel-footer text-center">
            <div class="pagination"></div>
        </div>
    </div>
</div>
    
<script src="js/jquery-1.10.2.js" type="text/javascript"></script>
<script src="js/bootstrap.min.js" type="text/javascript"></script>
<script src="js/jquery.bootpag.min.js" type="text/javascript"></script>

<script type="text/javascript">
$(document).ready(function() {
    $("#pg-results").load("fetch_data.php");
    $(".pagination").bootpag({
        total: <?php echo $total_pages; ?>,
        page: 1,
        maxVisible: 5
    }).on("page", function(e, page_num){
        e.preventDefault();
        /*$("#results").prepend('<div class="loading-indication"><img src="ajax-loader.gif" /> Loading...</div>');*/
        $("#pg-results").load("fetch_data.php", {"page": page_num});
    });
});
</script>

</body>
</html>

Do you notice that we haven't used any ajax() function in our script? Here's where the bootpag plugin comes into play. It takes care of ajax call at the background. All you have to do is, use the plugin's load() method to load the page results and bootpag() method to display pagination links and the rest of the things will be taken care automatically.

fetch_data.php

This script will be executed during ajax request. It pulls of the records from the database by using LIMIT Query and returns only a small set of rows to be displayed on a particular page.

<?php
include_once("db_connect.php");

if (isset($_POST["page"])) {
    $page_no = filter_var($_POST["page"], FILTER_SANITIZE_NUMBER_INT, FILTER_FLAG_STRIP_HIGH);
    if(!is_numeric($page_no))
        die("Error fetching data! Invalid page number!!!");
} else {
    $page_no = 1;
}

// get record starting position
$start = (($page_no-1) * $row_limit);

$results = $pdo->prepare("SELECT * FROM customers ORDER BY id LIMIT $start, $row_limit");
$results->execute();

while($row = $results->fetch(PDO::FETCH_ASSOC)) {
    echo "<tr>" . 
    "<td>" . $row['id'] . "</td>" . 
    "<td>" . $row['name'] . "</td>" . 
    "<td>" . $row['email'] . "</td>" . 
    "<td>" . $row['city'] . "</td>" . 
    "</tr>";
}
?>

That's it! Now we have the complete set of coding for ajax driven pagination using php & mysql. Run index.php and you can see the records displayed on html table with pagination links.

Initially it will show you the results for the first page and you have to click on the page links to navigate to other pages.

simple-pagination-bootstrap-jquery-php-pdo

Also Read:

I hope you find this simple ajax pagination script with jquery, php pdo & mysql useful. The code is highly customizable and you have all the required things in place. If you have any queries then please let me know through comments.

Simple AJAX Pagination in jQuery, PHP PDO & MySQL

2 comments:

Contact Form