Pagination Example using Ajax and PHP

Post Views: 6,167

Hello Friends, Hope you are doing well, today we are going to see a pagination example using Ajax and PHP. The same concept I had used in one of my project few months ago and again yesterday, actually I can say many of the times but every time I don’t remember code and have to find in old codes but today I thought to make it live so that I don’t waste time finding in my old codes instead just come here and search it. Here in this example I have used two w3schools resources for product view ui and pagination ui, link given below.

https://www.w3schools.com/howto/howto_css_product_card.asp
https://www.w3schools.com/howto/howto_css_pagination.asp

Before starting the code we have to create database and table and also need insert some sample data in it. So I am sharing SQL query below as well for creating table and inserting sample data. You have to do nothing just create database using phpMyAdmin and execute below query.

-- phpMyAdmin SQL Dump
-- version 5.0.1
-- https://www.phpmyadmin.net/
--
-- Host: localhost
-- Generation Time: Apr 26, 2020 at 11:45 AM
-- Server version: 10.4.11-MariaDB
-- PHP Version: 7.4.2

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `test_db`
--

-- --------------------------------------------------------

--
-- Table structure for table `products`
--

CREATE TABLE `products` (
  `id` int(11) NOT NULL,
  `name` varchar(250) NOT NULL,
  `slug` varchar(250) NOT NULL,
  `price` decimal(10,2) NOT NULL,
  `photo` varchar(512) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `products`
--

INSERT INTO `products` (`id`, `name`, `slug`, `price`, `photo`) VALUES
(1, 'Product 1', 'product-1', '200.00', ''),
(2, 'Product 2', 'product-2', '250.00', ''),
(3, 'Product 3', 'product-3', '200.00', ''),
(4, 'Product 4', 'product-4', '300.00', ''),
(5, 'Product 5', 'product-5', '350.00', ''),
(6, 'Product 6', 'product-6', '350.00', ''),
(7, 'Product 7', 'product-7', '300.00', ''),
(8, 'Product 8', 'product-8', '250.00', ''),
(9, 'Product 9', 'product-9', '300.00', ''),
(10, 'Product 10', 'product-10', '300.00', ''),
(11, 'Product 11', 'product-11', '350.00', ''),
(12, 'Product 12', 'product-12', '250.00', ''),
(13, 'Product 13', 'product-13', '250.00', ''),
(14, 'Product 14', 'product-14', '300.00', ''),
(15, 'Product 20', 'product-20', '300.00', ''),
(16, 'Product 21', 'product-21', '305.00', ''),
(17, 'Product 22', 'product-22', '350.00', '');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `products`
--
ALTER TABLE `products`
  ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `products`
--
ALTER TABLE `products`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=18;
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

-- phpMyAdmin SQL Dump

-- version 5.0.1

-- https://www.phpmyadmin.net/

-- Host: localhost

-- Generation Time: Apr 26, 2020 at 11:45 AM

-- Server version: 10.4.11-MariaDB

-- PHP Version: 7.4.2

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";

SET AUTOCOMMIT = 0;

START TRANSACTION;

SET time_zone = "+00:00";

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;

/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;

/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;

/*!40101 SET NAMES utf8mb4 */;

-- Database: `test_db`

-- --------------------------------------------------------

-- Table structure for table `products`

CREATE TABLE `products` (

`id` int(11) NOT NULL,

`name` varchar(250) NOT NULL,

`slug` varchar(250) NOT NULL,

`price` decimal(10,2) NOT NULL,

`photo` varchar(512) NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Dumping data for table `products`

INSERT INTO `products` (`id`, `name`, `slug`, `price`, `photo`) VALUES

(1, 'Product 1', 'product-1', '200.00', ''),

(2, 'Product 2', 'product-2', '250.00', ''),

(3, 'Product 3', 'product-3', '200.00', ''),

(4, 'Product 4', 'product-4', '300.00', ''),

(5, 'Product 5', 'product-5', '350.00', ''),

(6, 'Product 6', 'product-6', '350.00', ''),

(7, 'Product 7', 'product-7', '300.00', ''),

(8, 'Product 8', 'product-8', '250.00', ''),

(9, 'Product 9', 'product-9', '300.00', ''),

(10, 'Product 10', 'product-10', '300.00', ''),

(11, 'Product 11', 'product-11', '350.00', ''),

(12, 'Product 12', 'product-12', '250.00', ''),

(13, 'Product 13', 'product-13', '250.00', ''),

(14, 'Product 14', 'product-14', '300.00', ''),

(15, 'Product 20', 'product-20', '300.00', ''),

(16, 'Product 21', 'product-21', '305.00', ''),

(17, 'Product 22', 'product-22', '350.00', '');

-- Indexes for dumped tables

-- Indexes for table `products`

ALTER TABLE `products`

ADD PRIMARY KEY (`id`);

-- AUTO_INCREMENT for dumped tables

-- AUTO_INCREMENT for table `products`

ALTER TABLE `products`

MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=18;

COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;

/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

Note: I have also shared the download link of this dump at the end of this post

Now we can start code

config.php

<?php

function getConnection() {
    $servername = "localhost";
    $username = "root";
    $password = "";
    $dbname = "test_db";

    // Create connection
    $conn = new mysqli($servername, $username, $password, $dbname);
    // Check connection
    if (mysqli_connect_error()) {
        die("Database connection failed: " . mysqli_connect_error());
    } else {
        return $conn;
    }
}

define("T_PRODUCTS", "products");

function getProducts($columns = array(), $filters = array(), $offset = 0, $limit = 12, $order_by = "id", $order = "ASC") {
    $conn = getConnection();
    $data = array();
    $sql = "SELECT * FROM " . T_PRODUCTS . " WHERE 1";    
    if (!empty($columns) && is_array($columns)) {
        $sql = "SELECT `" . implode("`,`", $columns) . "` FROM " . T_PRODUCTS . " WHERE 1";
    }
    if (isset($filters['query']) && trim($filters['query']) <> "") {
        $q = mysqli_real_escape_string($conn, $filters['query']);
        $sql .= " AND (name LIKE '%{$q}%' OR price LIKE '%{$q}%')";
    }
    $sql .= " ORDER BY {$order_by} {$order}";
    if ($limit != -1 && is_numeric($offset) && is_numeric($limit)) {
        $sql .= " LIMIT {$offset}, {$limit}";
    }
    $result = $conn->query($sql);
    $GLOBALS['queryerrormsg'] = $conn->error;
    while ($row = $result->fetch_assoc()) {
        $data[] = $row;
    }
    return $data;
}

<?php

function getConnection() {

$servername = "localhost";

$username = "root";

$password = "";

$dbname = "test_db";

// Create connection

$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection

if (mysqli_connect_error()) {

die("Database connection failed: " . mysqli_connect_error());

} else {

return $conn;

}

define("T_PRODUCTS", "products");

function getProducts($columns = array(), $filters = array(), $offset = 0, $limit = 12, $order_by = "id", $order = "ASC") {

$conn = getConnection();

$data = array();

$sql = "SELECT * FROM " . T_PRODUCTS . " WHERE 1";

if (!empty($columns) && is_array($columns)) {

$sql = "SELECT `" . implode("`,`", $columns) . "` FROM " . T_PRODUCTS . " WHERE 1";

}

if (isset($filters['query']) && trim($filters['query']) <> "") {

$q = mysqli_real_escape_string($conn, $filters['query']);

$sql .= " AND (name LIKE '%{$q}%' OR price LIKE '%{$q}%')";

}

$sql .= " ORDER BY {$order_by} {$order}";

if ($limit != -1 && is_numeric($offset) && is_numeric($limit)) {

$sql .= " LIMIT {$offset}, {$limit}";

}

$result = $conn->query($sql);

$GLOBALS['queryerrormsg'] = $conn->error;

while ($row = $result->fetch_assoc()) {

$data[] = $row;

}

return $data;

}

requests.php

<?php

include_once 'config.php';

if (!isset($_REQUEST['action'])) {
    echo "Please provide action parameter";
    die();
}

$action = trim($_REQUEST['action']);

if ($action == "get-products") {
    $response['code'] = '0';
    $filters['query'] = isset($_REQUEST['s']) ? filter_var(trim($_REQUEST['s']), FILTER_SANITIZE_STRING) : "";

    /* pagination logic start */
    $items_count = count(getProducts(array('id'), $filters, 0, -1));
    $items_per_page = isset($_REQUEST['per_page']) ? $_REQUEST['per_page'] : 5;
    $items_per_page = $items_per_page > 50 ? 50 : $items_per_page;
    $max_pages = intval($items_count / $items_per_page + 1);
    $current_page = !isset($_REQUEST['paged']) || intval($_REQUEST['paged']) < 1 ? 1 : filter_var(trim($_REQUEST['paged']), FILTER_SANITIZE_NUMBER_INT);
    $current_page = $current_page > $max_pages ? $max_pages : $current_page;
    $offset = $items_per_page * $current_page - $items_per_page;
    /* pagination logic end */

    $order_by = (isset($_REQUEST['order_by']) && in_array(trim($_REQUEST['order_by']), array("id", "name", "price"))) ? trim($_REQUEST['order_by']) : 'id';
    $order = (isset($_REQUEST['order']) && in_array(trim($_REQUEST['order']), array("ASC", "DESC"))) ? trim($_REQUEST['order']) : 'DESC';
    
    $response['products'] = getProducts(array(), $filters, $offset, $items_per_page, $order_by, $order);
    $response['page'] = $current_page;
    $response['pages'] = $max_pages;
    $response['msg'] = "success";
    
    header("Content-Type:application/json");
    echo json_encode($response);
    exit();
}

<?php

include_once 'config.php';

if (!isset($_REQUEST['action'])) {

echo "Please provide action parameter";

die();

}

$action = trim($_REQUEST['action']);

if ($action == "get-products") {

$response['code'] = '0';

$filters['query'] = isset($_REQUEST['s']) ? filter_var(trim($_REQUEST['s']), FILTER_SANITIZE_STRING) : "";

/* pagination logic start */

$items_count = count(getProducts(array('id'), $filters, 0, -1));

$items_per_page = isset($_REQUEST['per_page']) ? $_REQUEST['per_page'] : 5;

$items_per_page = $items_per_page > 50 ? 50 : $items_per_page;

$max_pages = intval($items_count / $items_per_page + 1);

$current_page = !isset($_REQUEST['paged']) || intval($_REQUEST['paged']) < 1 ? 1 : filter_var(trim($_REQUEST['paged']), FILTER_SANITIZE_NUMBER_INT);

$current_page = $current_page > $max_pages ? $max_pages : $current_page;

$offset = $items_per_page * $current_page - $items_per_page;

/* pagination logic end */

$order_by = (isset($_REQUEST['order_by']) && in_array(trim($_REQUEST['order_by']), array("id", "name", "price"))) ? trim($_REQUEST['order_by']) : 'id';

$order = (isset($_REQUEST['order']) && in_array(trim($_REQUEST['order']), array("ASC", "DESC"))) ? trim($_REQUEST['order']) : 'DESC';

$response['products'] = getProducts(array(), $filters, $offset, $items_per_page, $order_by, $order);

$response['page'] = $current_page;

$response['pages'] = $max_pages;

$response['msg'] = "success";

header("Content-Type:application/json");

echo json_encode($response);

exit();

}

index.php

<!DOCTYPE html>
<html>
    <head>
        <meta charset="UTF-8">
        <title>Products List - Ajax Pagination</title>
        <style>
            .card {
                box-shadow: 0 4px 8px 0 rgba(0, 0, 0, 0.2);
                max-width: 300px;
                margin: auto;
                text-align: center;
                font-family: arial;
                list-style: none;
                float: left;
                margin: 10px;
            }

            .price {
                color: grey;
                font-size: 22px;
            }

            .card button {
                border: none;
                outline: 0;
                padding: 12px;
                color: white;
                background-color: #000;
                text-align: center;
                cursor: pointer;
                width: 100%;
                font-size: 18px;
            }

            .card button:hover {
                opacity: 0.7;
            }

            /* Pagination links */
            .pagination a {
                color: black;
                float: left;
                padding: 8px 16px;
                text-decoration: none;
                transition: background-color .3s;
            }

            /* Style the active/current link */
            .pagination a.active {
                background-color: dodgerblue;
                color: white;
            }

            /* Add a grey background color on mouse-over */
            .pagination a:hover:not(.active) {background-color: #ddd;} 
        </style>
    </head>
    <body>
        <h1>
            Products
            <input type="text" id="search" placeholder="Press Enter After Typing Your Query" style="float: right;"/>
        </h1>
        <ul id="products-list">
            
        </ul>
        <div style="clear: both;"></div>
        <div class="pagination" style="margin-left: 35px; margin-top: 10px;">
            
        </div>

        <script src="https://code.jquery.com/jquery-3.5.0.min.js" integrity="sha256-xNzN2a4ltkB44Mc/Jz3pT4iU1cmeR0FkXs4pru/JxaQ=" crossorigin="anonymous"></script>
        <script>
            $("#search").keypress(function(event){
                if(event.keyCode == "13") { 
                    load(1);
                } 
            });
            $(".pagination").on("click", "a", function () {
                load($(this).attr("page"));
            });

            function load(page) {
                var data = new FormData();
                data.append("s", $("#search").val());

                $.ajax({
                    type: 'POST',
                    url: "requests.php?action=get-products&paged=" + page,
                    data: data,
                    contentType: false,
                    processData: false,

                }).done(function (data) {
                    if (data.code === '0') {
                        $('#products-list').find('.card').remove();
                        for (var i = 0; i < data.products.length; i++) {
                            var template = $('#product-template').text();
                            var imgsrc = data.products[i].photo ? data.products[i].photo : "http://placehold.it/220x220";
                            template = template.replace('%%imgsrc%%', imgsrc);
                            template = template.replace('%%alt%%', data.products[i].name);
                            template = template.replace('%%name%%', data.products[i].name.substring(0, 25));
                            template = template.replace('%%price%%', data.products[i].price);
                            $('#products-list').append(template);
                        }
                        
                        var pages = '<a href="javascript:;" page="' + (parseInt(data.page) - 1) + '">&laquo;</a>';
                        for (var i = 1; i <= data.pages; i++) {
                            pages += '<a href="javascript:;" page="' + i + '" class="' + (data.page == i ? 'active' : '') + '">' + i + '</a>';
                        }
                        pages += '<a href="javascript:;" page="' + (parseInt(data.page) + 1) + '">&raquo;</a>';
                        $(".pagination").html(pages);

                    }
                }).fail(function (data) {
                    //any message
                });
            }
            //load first page
            load(1);
        </script>
        <!-- product template -->
        <script type="text/html" id="product-template">
            <li class="card">
                <img src="%%imgsrc%%" alt="%%alt%%" style="width:100%">
                <h1>%%name%%</h1>
                <p class="price">₹%%price%%</p>
                <p><button>Add to Cart</button></p>
            </li>
        </script>
    </body>
</html>

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

<!DOCTYPE html>

<html>

<head>

<title>Products List - Ajax Pagination</title>

<style>

.card {

box-shadow: 0 4px 8px 0 rgba(0, 0, 0, 0.2);

max-width: 300px;

margin: auto;

text-align: center;

font-family: arial;

list-style: none;

float: left;

margin: 10px;

}

.price {

color: grey;

font-size: 22px;

}

.card button {

border: none;

outline: 0;

padding: 12px;

color: white;

background-color: #000;

text-align: center;

cursor: pointer;

width: 100%;

font-size: 18px;

}

.card button:hover {

opacity: 0.7;

}

/* Pagination links */

.pagination a {

color: black;

float: left;

padding: 8px 16px;

text-decoration: none;

transition: background-color .3s;

}

/* Style the active/current link */

.pagination a.active {

background-color: dodgerblue;

color: white;

}

/* Add a grey background color on mouse-over */

.pagination a:hover:not(.active) {background-color: #ddd;}

</style>

</head>

<body>

<h1>

Products

</h1>

</ul>

</div>

$("#search").keypress(function(event){

if(event.keyCode == "13") {

load(1);

}

});

$(".pagination").on("click", "a", function () {

load($(this).attr("page"));

});

function load(page) {

var data = new FormData();

data.append("s", $("#search").val());

$.ajax({

type: 'POST',

url: "requests.php?action=get-products&paged=" + page,

data: data,

contentType: false,

processData: false,

}).done(function (data) {

if (data.code === '0') {

$('#products-list').find('.card').remove();

for (var i = 0; i < data.products.length; i++) {

var template = $('#product-template').text();

var imgsrc = data.products[i].photo ? data.products[i].photo : "http://placehold.it/220x220";

template = template.replace('%%imgsrc%%', imgsrc);

template = template.replace('%%alt%%', data.products[i].name);

template = template.replace('%%name%%', data.products[i].name.substring(0, 25));

template = template.replace('%%price%%', data.products[i].price);

$('#products-list').append(template);

}

var pages = '<a href="javascript:;" page="' + (parseInt(data.page) - 1) + '">«</a>';

for (var i = 1; i <= data.pages; i++) {

pages += '<a href="javascript:;" page="' + i + '" class="' + (data.page == i ? 'active' : '') + '">' + i + '</a>';

}

pages += '<a href="javascript:;" page="' + (parseInt(data.page) + 1) + '">»</a>';

$(".pagination").html(pages);

}

}).fail(function (data) {

//any message

});

}

//load first page

load(1);

</script>

<p class="price">₹%%price%%</p>

</li>

</script>

</body>

</html>

Video

SQL Dump Download

Ajax Pagination SQL Dump

1 file(s) 2.25 KB

Download

NetBeans Project Download

Ajax Pagination NetBeans Project

1 file(s) 5.54 KB

Download

Thanks friends
Your queries & suggestions are welcome in comments section
Please don’t forget to share if you find this helpful

Ajax Pagination SQL Dump

Ajax Pagination NetBeans Project

Comments

Leave a Reply Cancel reply