In this article, we will learn how to make bootstrap pagination in PHP and MySQL


Example

We need some dummy data to show in pagination for that simple just create a database table and import the following data in your PHPMyAdmin.


Project structure

php-tutorial/
db_config.php
┗ index.php

php-tutorial.sql

-- phpMyAdmin SQL Dump
-- version 5.0.4
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1
-- Generation Time: Jan 26, 2021 at 04:09 AM
-- Server version: 10.4.17-MariaDB
-- PHP Version: 7.3.25

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
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: `php-tutorial`
--

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

--
-- Table structure for table `myposts`
--

CREATE TABLE `myposts` (
  `id` int(11) NOT NULL,
  `title` varchar(250) COLLATE utf8_unicode_ci NOT NULL,
  `postdate` varchar(250) COLLATE utf8_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Dumping data for table `myposts`
--

INSERT INTO `myposts` (`id`, `title`, `postdate`) VALUES
(66, 'Using htaccess to deny access to files and directories', '22-11-2020'),
(68, 'PUBG Mobile India Full Game Download Android And iOS', '22-11-2020'),
(69, 'Bluestacks - How to play mobile games in PC', '22-11-2020'),
(70, 'Avada - Website Builder For WordPress and WooCommerce download as free', '22-11-2020'),
(71, 'Download Farming Simulator 19 For Free 2020', '23-11-2020'),
(72, 'How To Download Free Grand Theft Auto V For PC', '23-11-2020'),
(73, 'How to download WhatsApp Desktop Or PC', '23-11-2020'),
(74, 'Pagination in Codeigniter With Bootstrap 4', '23-11-2020'),
(75, 'How To Use Phone by Google Caller ID and spam protection App', '23-11-2020'),
(76, 'Minecraft Caves and Clifs 1.17 Android Download', '24-11-2020'),
(77, 'PHP Date and Time - complete tutorial for beginners', '24-11-2020'),
(78, 'High-speed Net - Secret Jio APN Settings', '24-11-2020'),
(79, 'How to upload multiple files into CodeIgniter', '25-11-2020'),
(80, 'How to upload multiple files with JavaScript and PHP', '25-11-2020'),
(81, 'Crash Bandicoot 4 Its About Time Full Game For PC Free Download', '26-11-2020'),
(82, '5 Easy ways to make money online and quickly', '26-11-2020'),
(83, 'How to Complete 4000 Hours of Watch Time on YouTube This Year As A Small Creator', '26-11-2020'),
(84, 'Zip And Unzip Files in Windows Pc', '26-11-2020'),
(85, 'How To Create And Use Google Form', '26-11-2020'),
(86, 'How To Create A Paytm Account With KYC', '27-11-2020'),
(87, 'Fixnow - Plumbing HTML Template download as free', '27-11-2020'),
(88, 'Tips And Tricks to increase PC performance in Windows 10', '27-11-2020'),
(89, 'Error 0x80070002 - Windows Update', '27-11-2020'),
(90, 'How to stop automatic updates on Windows 10', '28-11-2020'),
(91, 'How to speed up your internet speed', '28-11-2020'),
(92, 'How to make your Facebook profile as private', '28-11-2020'),
(94, 'The most effective method to Import CSV file data to MySQL in CodeIgniter', '28-11-2020'),
(96, 'How to create and use UPI', '01-12-2020'),
(97, '2 Ways to activate Windows 10 FREE without additional software', '01-12-2020'),
(98, 'Jio Free Data Tricks 2021', '02-12-2020'),
(99, 'How To Download Among Us PC For Free', '02-12-2020'),
(100, 'How can I find out which graphics card I have in my PC', '02-12-2020'),
(101, 'How to Make Money with Your Blog in 2021', '02-12-2020'),
(102, 'How to Format USB Flash Drive Using CMD', '02-12-2020'),
(103, 'Troubleshoot problems updating Windows 10', '02-12-2020'),
(104, 'How to Create a Bootable Pendrive using cmd', '02-12-2020'),
(105, '15 best Android games on playstore', '03-12-2020'),
(107, 'OnePlus 8T buyers guide - Everything you need to know', '03-12-2020'),
(108, 'PUBG Mobile India Version Latest Update- APK Download LinksAvailable on Official Website', '03-12-2020'),
(109, '7 Ways to Improve Your Computer Performance', '03-12-2020'),
(110, 'Top 5 Product Review Website Rating Blog HTML5 Templates', '03-12-2020'),
(111, 'Top 5 best Android emulators for PC and Mac of 2021', '04-12-2020'),
(112, 'The best Android phones - January 2021', '04-12-2020'),
(114, 'Cybersecurity Insights Report On 5G release', '14-12-2020'),
(115, 'GOOGLE adds a list of new partners with BeyondCorp Alliance', '14-12-2020'),
(116, 'PUBG Mobile India Download Now Available Beta APK', '14-12-2020'),
(117, 'Very high resolution images free download', '14-12-2020'),
(118, 'What is Creative Commons', '15-12-2020'),
(119, 'What is a public domain', '15-12-2020'),
(120, 'What is royalty free', '15-12-2020'),
(121, '15 best Chromebook games from Google Play', '15-12-2020'),
(122, 'Organizations may fail to train their employees against Cybersecurity Threats', '16-12-2020'),
(123, 'One Identity Highlights Barriers to Adoption of Zero Trust Framework', '17-12-2020'),
(125, '5 YouTube tricks to help you raise revenue 50 percentage faster Instantly', '17-12-2020'),
(126, 'PUBG Mobile 2 global version beta update APK OBB download', '19-12-2020'),
(127, 'The 15 best Android apps available now', '19-12-2020'),
(128, 'Can not send or receive messages on your Android phone', '19-12-2020'),
(129, 'Full Galaxy S21 Ultra specs leak no in-box charger allegedly confirmed', '19-12-2020'),
(130, 'Google Camera Go now brings HDR to budget smartphones Updated', '19-12-2020'),
(131, 'Sony returns all Cyberpunk 2077 order renewals: Xbox and refunds for all', '19-12-2020'),
(132, 'How To Calculate CPC and CTR-Formulas', '20-12-2020'),
(133, 'How to move the mouse cursor with the keyboard in Windows', '20-12-2020'),
(135, 'Top 10 Indian Information Technology Companies', '21-12-2020'),
(136, 'Top Indian Information Technology Companies', '21-12-2020'),
(137, 'TOP MOST EXPENSIVE MOBILE PHONES IN THE WORD', '21-12-2020'),
(138, 'Best budget gaming headphones', '21-12-2020'),
(139, '10 WordPress Tips to Make Your Website Secure', '21-12-2020'),
(140, 'IPhone12 features How Apples snappy new magnet feature works', '21-12-2020'),
(141, 'How to Not Get Locked Out With Two-Factor Authentication', '21-12-2020'),
(142, '10 best reminder apps for Android', '21-12-2020'),
(143, 'BEST STORY-DRIVEN GAMES ON MOBILE', '23-12-2020'),
(144, 'Best top 5 Smart TVs in India', '23-12-2020'),
(145, 'SEO-friendly URLs', '24-12-2020'),
(146, 'What is Canonical Tag Or link SEO', '24-12-2020'),
(147, 'How to Increase Website Traffic Tips and Tricks', '26-12-2020');

--
-- Indexes for dumped tables
--

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

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `myposts`
--
ALTER TABLE `myposts`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=149;
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 */;


db_config.php

<?php

$hostname = "localhost";
$username = "root";
$password = "";

try {
    $connection = new PDO("mysql:host=$hostname;dbname=php-tutorial", $username, $password);
    // set the PDO error mode to exception
    $connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
    echo "Database connection failed: " . $e->getMessage();
}
?>


index.php

<?php
// Database
include('db_config.php');

// Set session
session_start();
if (isset($_POST['records-limit'])) {
    $_SESSION['records-limit'] = $_POST['records-limit'];
}

$limit = isset($_SESSION['records-limit']) ? $_SESSION['records-limit'] : 5;
$page = (isset($_GET['page']) && is_numeric($_GET['page']) ) ? $_GET['page'] : 1;
$paginationStart = ($page - 1) * $limit;
$posts = $connection->query("SELECT * FROM myposts LIMIT $paginationStart, $limit")->fetchAll();

// Get total records
$sql = $connection->query("SELECT count(id) AS id FROM myposts")->fetchAll();
$allRecrods = $sql[0]['id'];

// Calculate total pages
$totoalPages = ceil($allRecrods / $limit);

// Prev + Next
$prev = $page - 1;
$next = $page + 1;
?>

<!doctype html>
<html lang="en">

    <head>
        <meta charset="utf-8">
        <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
        <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/css/bootstrap.min.css">
        <title>PHP Pagination with MySQL and Bootstrap Example</title>
        <style>
            .container {
                max-width: 1000px
            }

            .custom-select {
                max-width: 150px
            }
        </style>
    </head>

    <body>
        <div class="container mt-5">
            <h2 class="text-center mb-5">PHP Pagination</h2>
            <!-- Select dropdown -->
            <div class="d-flex flex-row-reverse bd-highlight mb-3">
                <form action="index.php" method="post">
                    <select name="records-limit" id="records-limit" class="custom-select">
                        <option disabled selected>Records Limit</option>
                        <?php foreach ([5, 7, 10, 12] as $limit) : ?>
                            <option
                            <?php if (isset($_SESSION['records-limit']) && $_SESSION['records-limit'] == $limit) echo 'selected'; ?>
                                value="<?= $limit; ?>">
                                    <?= $limit; ?>
                            </option>
                        <?php endforeach; ?>
                    </select>
                </form>
            </div>

            <!-- Datatable -->
            <table class="table table-bordered mb-5">
                <thead>
                    <tr class="table-success">
                        <th scope="col">#</th>
                        <th scope="col">Title</th>
                        <th scope="col">Post date</th>

                    </tr>
                </thead>
                <tbody>
                    <?php foreach ($posts as $post): ?>
                        <tr>
                            <th scope="row"><?php echo $post['id']; ?></th>
                            <td><?php echo $post['title']; ?></td>
                            <td><?php echo $post['postdate']; ?></td>
                        </tr>
                    <?php endforeach; ?>
                </tbody>
            </table>

            <!-- Pagination -->
            <nav aria-label="Page navigation example mt-5">
                <ul class="pagination justify-content-center">
                    <li class="page-item <?php
                    if ($page <= 1) {
                        echo 'disabled';
                    }
                    ?>">
                        <a class="page-link"
                           href="<?php
                           if ($page <= 1) {
                               echo '#';
                           } else {
                               echo "?page=" . $prev;
                           }
                           ?>">Previous</a>
                    </li>

                    <?php for ($i = 1; $i <= $totoalPages; $i++): ?>
                        <li class="page-item <?php
                        if ($page == $i) {
                            echo 'active';
                        }
                        ?>">
                            <a class="page-link" href="index.php?page=<?= $i; ?>"> <?= $i; ?> </a>
                        </li>
                    <?php endfor; ?>

                    <li class="page-item <?php
                    if ($page >= $totoalPages) {
                        echo 'disabled';
                    }
                    ?>">
                        <a class="page-link"
                           href="<?php
                           if ($page >= $totoalPages) {
                               echo '#';
                           } else {
                               echo "?page=" . $next;
                           }
                           ?>">Next</a>
                    </li>
                </ul>
            </nav>
        </div>

        <!-- jQuery + Bootstrap JS -->
        <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>

        <script>
            $(document).ready(function() {
                $('#records-limit').change(function() {
                    $('form').submit();
                })
            });
        </script>
    </body>

</html>

Output: