Server-Side Table Pagination using PHP, AngularJS, and Bootstrap 5 Tutorial

Submitted by oretnom23 on Friday, November 25, 2022 - 16:52.

In this tutorial, you will learn how to Create a Server-Side Table Pagination using PHP, MySQL Database, AngularJS, and Bootstrap 5 Framework. The main purpose of this tutorial is to provide the IT/CS students and new programmers with a reference for implementing a Server-Side Pagination. Here, snippets are provided and a sample web application source code is also provided and is free to download.

What is Server-Side Table Pagination?

Server-Side Table Pagination is one of the common features of web applications. This feature helps to optimize the speed of the page load. It is a process of dividing the data into discrete pages. It also prevents exhausting the database of retrieving data.

How to Create a Server-Side Table Pagination in PHP, JavaScript, and AngularJS?

To create a Server-Side Table pagination using AngularJS, we will use $scope, $http, and $compile API. Using JavaScript and Bootstrap 5 Framework, we can create a script that generates the pagination buttons dynamically. And on the server side, using PHP and MySQL Database, we can query the limited data using LIMIT and OFFSET clauses of MySQL. Check out the snippets below that I prepared for this tutorial to have some idea of how to achieve this tutorial's objective.

Sample Web Application Snippets

The following snippets of the simple web application demonstrate the Server-Side Table Pagination.

Technologies

Here are the following technologies used to develop the snippets below.

HTML
CSS
JavaScript
PHP
MySQL Database
AngularJS
Bootstrap 5 Framework

Database

For this tutorial purposes only, let's create a new database named dummy_db and use the following MySQL Schema and Table Data.

 
    CREATE TABLE `employee_list` (
      `id` mediumint(8) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
      `name` varchar(255) DEFAULT NULL,
      `phone` varchar(100) DEFAULT NULL,
      `address` varchar(255) DEFAULT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
 
 
    INSERT INTO `employee_list` (`id`, `name`, `phone`, `address`) VALUES
    (1, 'Bradley Charles', '1-387-631-7773', '201-7452 Interdum Rd.'),
    (2, 'Lysandra Schwartz', '1-329-483-7978', '9649 Dolor Road'),
    (3, 'Whitney Moran', '(392) 138-7649', 'Ap #502-4286 Elementum Av.'),
    (4, 'Flavia Adkins', '1-517-765-3895', '563-8222 At Street'),
    (5, 'Vladimir Bradley', '1-436-562-8352', 'P.O. Box 170, 9079 Amet, Ave'),
    (6, 'Asher Osborn', '(255) 217-5562', '332-4023 Ridiculus Street'),
    (7, 'Shaine Mathews', '1-528-220-9818', 'P.O. Box 865, 3851 Dui. Road'),
    (8, 'Donovan Todd', '1-615-939-1360', 'P.O. Box 637, 895 Ac, Avenue'),
    (9, 'Jackson Ward', '1-305-537-3752', '199-8650 Sapien. Rd.'),
    (10, 'Naida Michael', '(157) 545-9897', '502-3887 Ultrices. St.'),
    (11, 'Scarlet Willis', '1-761-717-4296', '9935 A, St.'),
    (12, 'Colette Carr', '1-586-832-2814', 'Ap #654-7677 Mattis Ave'),
    (13, 'Macy Pena', '1-322-550-6531', '6567 Malesuada Rd.'),
    (14, 'Rooney Mckinney', '(295) 257-5029', 'P.O. Box 444, 1418 Vivamus Street'),
    (15, 'Denton Wilder', '1-464-768-6866', '798 Dictum St.'),
    (16, 'Jin Emerson', '(295) 421-5258', '394-5031 Risus. Rd.'),
    (17, 'Jack Rose', '(716) 946-1411', 'Ap #511-7704 Odio Rd.'),
    (18, 'Eric Serrano', '(911) 717-7798', 'P.O. Box 760, 9708 Penatibus Road'),
    (19, 'Forrest Andrews', '1-657-248-8527', '600-6646 Tellus Rd.'),
    (20, 'Elijah Gregory', '(252) 814-5344', 'Ap #372-9501 Aliquet Avenue'),
    (21, 'Phoebe Moses', '1-332-384-8360', '746-9767 Suspendisse St.'),
    (22, 'William Peters', '(850) 847-5722', 'P.O. Box 382, 4880 Neque St.'),
    (23, 'Basil Parsons', '(751) 775-5828', 'Ap #497-4995 Per Rd.'),
    (24, 'Mufutau Bradley', '(935) 451-8759', '520-315 Congue, Avenue'),
    (25, 'Ciaran Bradshaw', '(788) 774-8911', 'Ap #388-1102 Magna Ave');

Database Connection

Next, in our source code folder, let's create a new PHP File and save it as db-connect.php. This file contains the PHP Script that connects our created database to our web application.

db-connect.php

<?php
$host = "localhost";
$username = "root";
$pw = "";
$db_name = "dummy_db";
 
$conn = new mysqli($host, $username, $pw, $db_name);
 
if(!$conn){
    die("Database Connection Failed");
}

Page Interface

Next, we'll create the page interface of the application which contains a table for listing the data from the database. The script contains the HTML elements and AngularJS that are needed for the application. Save the snippet below as index.php.

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Angular JS Table Pagination</title>
    <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.2.0/css/all.min.css" integrity="sha512-xh6O/CkQoPOWDdYTDqeRdPCVd1SpvCA9XXcUnZS2FmJNp1coAFzvtCN9BmamE+4aHK8yyUHUSCcJHgXloTyT2A==" crossorigin="anonymous" referrerpolicy="no-referrer" />
    <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/[email protected]/dist/css/bootstrap.min.css" integrity="sha384-Zenh87qX5JnK2Jl0vWa8Ck2rdkQ2Bzep5IDxbcnCeuOxjzrPF/et3URy9Bv1WTRi" crossorigin="anonymous">
    <script src="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.2.0/js/all.min.js" integrity="sha512-naukR7I+Nk6gp7p5TMA4ycgfxaZBJ7MO5iC3Fp6ySQyKFHOGfpkSZkYVWV5R7u7cfAicxanwYQ5D1e17EfJcMA==" crossorigin="anonymous" referrerpolicy="no-referrer"></script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/angular.js/1.8.3/angular.js" integrity="sha512-klc+qN5PPscoGxSzFpetVsCr9sryi2e2vHwZKq43FdFyhSAa7vAqog/Ifl8tzg/8mBZiG2MAKhyjH5oPJp65EA==" crossorigin="anonymous" referrerpolicy="no-referrer"></script>
    <script src="https://cdn.jsdelivr.net/npm/[email protected]/dist/js/bootstrap.bundle.min.js" integrity="sha384-OERcA2EqjJCMA+/3y+gxIOqMEjwtxJY7qPCqsdltbNJuaOe923+mo//f6V8Qbsw3" crossorigin="anonymous"></script>
    <script src="bootstrap5-custom-pagination.js"></script>
    <style>
        html, body{
            min-height:100%;
            width:100%;
        }
        tbody:empty:after{
            content:'No records found'
        }
    </style>
</head>
<body>
    <nav class="navbar navbar-expand-lg navbar-dark bg-primary bg-gradient">
        <div class="container">
            <a class="navbar-brand" href="./">Angular JS Table Pagination</a>
            <div>
                <a href="https://sourcecodester.com" class="text-light fw-bolder h6 text-decoration-none" target="_blank">SourceCodester</a>
            </div>
        </div>
    </nav>
    <div class="container-fluid px-5 my-3" ng-app="SampleApp" ng-controller="tblController">
        <div class="col-lg-8 col-md-10 col-sm-12 mx-auto">
            <div class="card rounded-0 shadow mb-3">
                <div class="card-header">
                    <div class="card-title"><b>Employee List</b></div>
                </div>
                <div class="card-body">
                    <div class="container-fluid">
                        <div class="alert alert-danger mb-3 rounded-0" ng-show="error">{{error_msg}}</div>
                        <div class="table-responsive">
                            <table class="table table-bordered table-striped">
                                <thead>
                                    <tr class="bg-primary bg-gradient text-light">
                                        <th class="text-center">Employee</th>
                                        <th class="text-center">Contact #</th>
                                        <th class="text-center">Address</th>
                                    </tr>
                                </thead>
                                <tbody>
                                    <tr ng-repeat="data in Employees">
                                        <td>{{data.name}}</td>
                                        <td>{{data.phone}}</td>
                                        <td>{{data.address}}</td>
                                    </tr>
                                </tbody>
                            </table>
                        </div>
 
                        <div class="bootstrap5-custom-pagination" 
                            data-max-length="5"
                            data-number-of-pages="{{NumberOfPages}}"
                            data-current-page="{{currentPage}}"
                            data-btn-callback="paginate"
                            >
                        </div>
                    </div>
                </div>
            </div>
        </div>
    </div>
    <script src="app.js"></script>
</body>
</html>

Retrieving the Data

Next, we'll create a PHP File that contains the script for fetching the sample data from the database. The script requires POST data of "limit" and currentPage which will be needed for fetching only the data of the table's current page. Save the snippet below as getEmployees.php.

<?php 
require_once('db-connect.php');
$limit = isset($_POST['limit']) ? $_POST['limit'] : 5;
$currentPage = $offset = isset($_POST['currentPage']) ? $_POST['currentPage'] : 1;
$offset = $currentPage > 1 ? ceil(($currentPage * $limit) -$limit) : 0;
$sqlTotal = "SELECT id FROM `employee_list`";
$sql = "SELECT * FROM `employee_list` order by `name` asc LIMIT {$limit} OFFSET {$offset}";
$query = $conn->query($sql);
$queryTotal = $conn->query($sqlTotal);
$data['employees'] = $query->fetch_all(MYSQLI_ASSOC);
$data['total'] = $queryTotal->num_rows;
 
echo json_encode($data);
$conn->close();

Pagination Buttons

Next, we'll create a custom JavaScript class that contains the script for generating the pagination buttons including the previous and next buttons. The script generates a button that uses Bootstrap 5 Framework's design. In my case, I named this file bootstrap5-custom-pagination.js, and is loaded at the index.php file.

class bs5cp_paginations_class {
    paginate(){
        // Get Pagination Node Elements
        this.bs5cp_paginations_els = document.querySelectorAll('.bootstrap5-custom-pagination') 
 
        // Generate Pagination Buttons
        this.bs5cp_paginations_els.forEach(function(element, index){
            element.innerHTML = "";
            var options = {
                maxLength: parseInt(element.getAttribute('data-max-length')) || 5,
                currentPage: parseInt(element.getAttribute('data-current-page')) || 1,
                totalBtns: parseInt(element.getAttribute('data-number-of-pages')) || 1,
                maxButtons: parseInt(element.getAttribute('data-buttons-max')) || 5,
                btn_callback: element.getAttribute('data-btn-callback') || null,
                previousText: element.getAttribute('data-previous-text') || "prev",
                nextText: element.getAttribute('data-next-text') || "next"
            }
 
            // Button Group Parent
            var btn_group = document.createElement('div')
            btn_group.setAttribute('role','group');
            btn_group.classList.add('btn-group');
 
 
            //Previous Button
            var prev_btn = document.createElement('button')
            prev_btn.setAttribute('type','button');
            prev_btn.classList.add('btn', 'btn-sm', 'rounded-0', 'border');
            var prev_page = parseInt(options.currentPage) - 1;
            prev_btn.setAttribute('ng-click',`${options.btn_callback}(${prev_page})`);
            if(options.currentPage == 1)
            prev_btn.setAttribute('disabled', true)
            prev_btn.innerHTML = options.previousText
            element.appendChild(prev_btn)
 
 
            //First Page Button
            var first_btn = document.createElement('button')
                first_btn.classList.add('btn', 'btn-sm', 'rounded-0', 'border');
                first_btn.setAttribute('type','button');
                first_btn.setAttribute('ng-click',`${options.btn_callback}(1)`);
                first_btn.innerHTML = 1
                if(options.currentPage == 1)
                first_btn.classList.add('active', 'btn-primary');
                element.appendChild(first_btn)
 
                //Ellipsis Button
                var ellipsis_btn = document.createElement('button')
                    ellipsis_btn.classList.add('btn', 'btn-sm', 'rounded-0', 'border');
                    ellipsis_btn.setAttribute('type','button');
                    ellipsis_btn.setAttribute('disabled',true);
                    ellipsis_btn.innerHTML = '...'
 
 
                //Dynamic Page Buttons
                var dynamic_btn = document.createElement('button')
                    dynamic_btn.classList.add('btn', 'btn-sm', 'rounded-0', 'border');
                    dynamic_btn.setAttribute('type','button');
                if(options.currentPage > 3 && options.totalBtns > 5)
                    element.appendChild(ellipsis_btn.cloneNode(true))
                if(options.totalBtns > 5){
                    var iteratePage = parseInt(options.currentPage > 2 ? options.currentPage : 3) ;
                    for(var $i = iteratePage; $i < (iteratePage + 3); $i++){
                        var new_page_btn = dynamic_btn.cloneNode(true)
                        var num =  $i -1;
                        if((iteratePage + 1) == options.totalBtns)
                            num =  $i -2;
                        if(iteratePage == options.totalBtns)
                            num =  $i -3;
                        new_page_btn.setAttribute('ng-click',`${options.btn_callback}(${num})`);
                        new_page_btn.innerHTML = num
                        if(options.currentPage == num)
                            new_page_btn.classList.add('active', 'btn-primary');
                        element.appendChild(new_page_btn)
                    }
                }else{
                    for(var $i = 2; $i < 5; $i++){
                        var new_page_btn = dynamic_btn.cloneNode(true)
                        new_page_btn.setAttribute('ng-click',`${options.btn_callback}(${$i})`);
                        new_page_btn.innerHTML = $i
                        if(options.currentPage == $i)
                        new_page_btn.classList.add('active', 'btn-primary');
                        element.appendChild(new_page_btn)
                    }
                }
                if(options.totalBtns > 5 && ((options.totalBtns - 2) > options.currentPage)){
                    element.appendChild(ellipsis_btn.cloneNode(true))
                }
 
 
            //Last Page Button
            if(options.totalBtns > 1){
                var last_btn = document.createElement('button')
                    last_btn.classList.add('btn', 'btn-sm', 'rounded-0', 'border');
                    last_btn.setAttribute('type','button');
                    last_btn.setAttribute('ng-click',`${options.btn_callback}(${options.totalBtns})`);
                    last_btn.innerHTML = options.totalBtns
                    if(options.currentPage == options.totalBtns)
                    last_btn.classList.add('active', 'btn-primary');
                    element.appendChild(last_btn)
            }
 
            //Next Page Button
 
            var next_btn = document.createElement('button')
            next_btn.setAttribute('type','button');
            next_btn.classList.add('btn', 'btn-sm', 'rounded-0', 'border');
            var next_page = parseInt(options.currentPage) + 1;
            next_btn.setAttribute('ng-click',`${options.btn_callback}(${next_page})`);
            if(options.currentPage == options.totalBtns)
            next_btn.setAttribute('disabled', true)
            next_btn.innerHTML = options.nextText
            element.appendChild(next_btn)
 
        })
    }
}
let bs5cp_paginations = new bs5cp_paginations_class();

AngularJS

Lastly, we'll create a JavaScript file that initiates the angular module of the application. Save the snippet below as app.js.

var mymodule = angular.module("SampleApp",[]);
 
/**
    * Angular Controller
    */
 
var Employee_list = mymodule.controller("tblController", function ($compile, $scope, $http){
    $scope.NumberOfPages = 0;
    $scope.currentPage = 1;
    $scope.paginationMaxBtn = 5;
    $scope.maxLength = 5;
    $scope.Employees={}
    $scope.error = false
    $scope.error_msg = ''
    /*
        * Get Employees
        */
 
    $scope.getEmployees = function(){
        var fdata = new FormData();
            fdata.append('currentPage', $scope.currentPage)
            fdata.append('limit', $scope.maxLength)
        $http.post('getEmployees.php', new URLSearchParams(fdata).toString() ,{
            headers:{
                'Content-Type' : 'application/x-www-form-urlencoded; charset=utf-8'
            }
        })
        .then(
            function (response){
                if(response.status == 200){
                    var data = response.data
                    if(!!data.employees && data.total){
                        $scope.Employees = data.employees;
                        $scope.NumberOfPages = Math.ceil(parseInt(data.total) / $scope.maxLength);
 
                        // Initiate Pagination
                        bs5cp_paginations.paginate();
                        document.querySelectorAll('.bootstrap5-custom-pagination').forEach(function(element){
                            element.childNodes.forEach(function(child_element){
                                $compile(child_element)($scope)
                            })
                        })
 
                    }else if(!!data.error){
                        $scope.error_msg = data.error
                    }else{
                        console.error(response)
                        $scope.error_msg = "Fetching Employee List failed due to some reasons."
                    }
                }else{
                    $scope.error_msg = "Fetching Employee List failed due to some reasons."
                    console.error(response)
                }
            },
            function (error){
                    $scope.error_msg = "Fetching Employee List failed due to some reasons."
                    console.error(error)
            }
        )
    }
 
    /**
        * Trigger Pagination
        * @param {int} page  
        */
    $scope.paginate = function(page){
        $scope.currentPage = page;
    }
 
    /**
        * Listen for Current Page Changes
        */
    $scope.$watch('currentPage', function(){
        $scope.getEmployees()
    })
    $scope.getEmployees()
 
})

Application Snapshot

Here is the following snapshot of the result of the snippets of the sample web application that demonstrates the Server-Side Table Pagination.

That's it! You can now test the sample application that we created on your end. I have provided also the complete source code zip file that I created for this tutorial. You can download it by clicking the Download Button below this article.

That's the end of this tutorial. I hope this Server-Side Table Pagination Tutorial helps you with what you are looking for and that you'll find it useful for your current and future web application projects.

Happy Coding :)

Add new comment

769 views