Pagination in PHP MySQL with Next and Previous Example

Leave a Comment on Pagination in PHP MySQL with Next and Previous Example

If you display data in a list in PHP with MySQL using pagination, and want to show only 10 data on one page of this list with next and previous buttons. In this tutorial, you will learn how to create dynamic pagination in php and mysql with next and previous button.

Dynamic Pagination in PHP MySQL with Next and Previous

Steps to create your dynamic pagination code in PHP MySQL with next and previous:

  • Step 1 – Create Table in MySQL Database
  • Step 2 – Setup Database Connection PHP File
  • Step 3 – PHP Pagination Code with Next and Previous

Step 1 – Create Table in MySQL Database

First of all, open your phpmyadmin and run the following sql query. To create table into your selected database:

CREATE TABLE `authors` (
  `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `first_name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `last_name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `email` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `birthdate` date NOT NULL,
  `added` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
INSERT INTO `authors` (`id`, `first_name`, `last_name`, `email`, `birthdate`, `added`) VALUES
(1, 'Christian', 'Hackett', '[email protected]', '1983-12-30', '1992-02-05 13:21:46'),
(2, 'Percy', 'Blanda', 'to\'[email protected]', '2011-09-19', '1990-04-24 01:17:02'),
(3, 'Kennedi', 'Crona', '[email protected]', '2013-12-17', '1973-03-17 13:21:12'),
(4, 'Jordan', 'Hessel', '[email protected]', '1975-04-17', '1970-10-18 14:43:11'),
(5, 'Ila', 'Von', '[email protected]', '1989-10-04', '2004-08-15 06:25:33'),
(6, 'Caitlyn', 'Legros', '[email protected]', '2020-02-05', '1978-01-05 20:54:52'),
(7, 'Jace', 'Mills', '[email protected]', '2017-04-30', '1999-12-06 17:56:43'),
(8, 'Kiley', 'Hickle', '[email protected]', '1999-09-16', '2014-05-27 22:54:34'),
(9, 'Keshaun', 'Swift', '[email protected]', '1984-05-27', '1979-06-15 02:41:44'),
(10, 'Bernhard', 'Hudson', '[email protected]', '1996-09-30', '1987-10-15 19:29:03'),
(11, 'Brando', 'Maggio', '[email protected]', '2001-10-16', '1989-08-31 08:25:57'),
(12, 'Kariane', 'Dicki', '[email protected]', '2006-03-25', '2018-10-07 06:23:34'),
(13, 'Earnestine', 'Ankunding', '[email protected]', '1975-11-11', '2019-08-20 17:12:29'),
(14, 'Nayeli', 'Schiller', '[email protected]', '2005-01-28', '2008-02-28 19:42:52'),
(15, 'Tressie', 'Willms', '[email protected]', '1995-11-24', '2000-05-19 09:48:39'),
(16, 'Shaun', 'Walsh', '[email protected]', '1991-11-01', '1976-03-24 11:54:20'),
(17, 'Roosevelt', 'Leuschke', '[email protected]', '1984-12-16', '2004-10-01 00:21:22'),
(18, 'Bill', 'Farrell', '[email protected]', '1986-03-18', '1994-01-12 02:22:08'),
(19, 'Maurice', 'Johns', '[email protected]', '2000-12-07', '2004-07-16 02:59:16'),
(20, 'Taya', 'Towne', '[email protected]', '1972-01-14', '2018-04-19 22:00:33'),
(21, 'Ivah', 'Kuhlman', '[email protected]', '2003-10-30', '2004-08-28 08:01:06'),
(22, 'Virgie', 'Quitzon', '[email protected]', '1977-06-30', '1990-08-13 05:30:49'),
(23, 'Laurel', 'Lueilwitz', '[email protected]', '1973-03-10', '2006-06-24 15:01:07'),
(24, 'Colton', 'Wisoky', '[email protected]', '2004-03-13', '1972-04-13 10:39:32'),
(25, 'Frankie', 'Kutch', '[email protected]', '1983-07-16', '1993-03-27 06:29:23'),
(26, 'Noelia', 'Kertzmann', '[email protected]', '1990-10-18', '1989-02-02 16:52:51'),
(27, 'Aida', 'Durgan', '[email protected]', '1979-05-30', '1996-08-20 08:45:41'),
(28, 'Vesta', 'Stiedemann', 'jo\'[email protected]', '2019-03-18', '1977-11-04 12:13:54'),
(29, 'Emmy', 'Armstrong', '[email protected]', '1971-07-24', '1997-08-23 02:34:33'),
(30, 'Melany', 'Kris', '[email protected]', '1970-05-03', '1993-01-11 04:26:59'),
(31, 'Valentine', 'Boyle', '[email protected]', '1988-02-08', '2012-11-15 12:54:23'),
(32, 'Trisha', 'Gutmann', '[email protected]', '1992-07-21', '1989-10-25 21:52:17'),
(33, 'Angela', 'Stoltenberg', '[email protected]', '1973-08-15', '2008-11-21 16:16:02'),
(34, 'Dulce', 'Bartoletti', '[email protected]', '2011-04-03', '2015-10-07 05:27:01'),
(35, 'Haylie', 'Rohan', '[email protected]', '2003-07-15', '2005-05-10 00:13:04'),
(36, 'Daphney', 'Nikolaus', '[email protected]', '1978-02-19', '1984-02-12 08:32:02'),
(37, 'Gabriella', 'Wolf', '[email protected]', '2009-11-28', '2001-10-20 06:25:35'),
(38, 'Elvie', 'Pfannerstill', '[email protected]', '2014-08-14', '2015-10-19 13:48:05'),
(39, 'Elliot', 'Denesik', '[email protected]', '2005-02-28', '2015-01-29 07:09:30'),
(40, 'Jermaine', 'Cartwright', '[email protected]', '2013-07-05', '1970-03-26 02:34:32'),
(41, 'Herminio', 'Rosenbaum', '[email protected]', '1997-10-06', '2010-07-25 08:32:11'),
(42, 'Mateo', 'Raynor', '[email protected]', '2006-11-04', '2017-08-25 06:13:30'),
(43, 'Maymie', 'Runte', '[email protected]', '2000-06-19', '2018-06-01 05:42:58'),
(44, 'Demond', 'Skiles', '[email protected]', '1983-02-22', '2013-08-11 14:39:05'),
(45, 'Arvel', 'Jones', '[email protected]', '1975-03-20', '1974-10-04 10:44:12'),
(46, 'Donavon', 'Thiel', '[email protected]', '1994-12-25', '2019-05-05 13:08:57'),
(47, 'Aiyana', 'Ziemann', '[email protected]', '1987-02-18', '1982-12-16 09:38:25'),
(48, 'Gillian', 'Streich', '[email protected]', '1976-07-07', '1990-09-03 09:25:48'),
(49, 'Bryon', 'Roob', '[email protected]', '1979-06-21', '1979-03-28 01:58:17'),
(50, 'Wendy', 'McLaughlin', '[email protected]', '2018-06-06', '2002-10-11 21:50:33');

Step 2 – Setup Database Connection PHP File

In this step, you need to connect your msyql database with php app. So, you need to create a file name db.php and add the following code into db.php file:

<?php
    $hostname = "localhost";
    $username = "root";
    $password = "";
    try {
        $connection = new PDO("mysql:host=$hostname;dbname=php_crud", $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();
    }
?>

Note that, This code is used to create a MySQL database connection in PHP project.

Step 3 – PHP Pagination Code with Next and Previous

In this step, create PHP file named index.php and add the below PHP and HTML code into index.php file.

Note that, This HTML code shows the user listing with pagination.

Now, add the following html form into index.php file:

<?php
  // Database
  include('db.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;
  $authors = $connection->query("SELECT * FROM authors LIMIT $paginationStart, $limit")->fetchAll();
  // Get total records
  $sql = $connection->query("SELECT count(id) AS id FROM authors")->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://stackpath.bootstrapcdn.com/bootstrap/4.5.0/css/bootstrap.min.css">
    <title>PHP Pagination Example - Tutsmake.com</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">Simple PHP Pagination Demo</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">First</th>
                    <th scope="col">Last</th>
                    <th scope="col">Email</th>
                    <th scope="col">DOB</th>
                </tr>
            </thead>
            <tbody>
                <?php foreach($authors as $author): ?>
                <tr>
                    <th scope="row"><?php echo $author['id']; ?></th>
                    <td><?php echo $author['first_name']; ?></td>
                    <td><?php echo $author['last_name']; ?></td>
                    <td><?php echo $author['email']; ?></td>
                    <td><?php echo $author['birthdate']; ?></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>

Explanation of above code:

The $limit variable sets the dynamic limit for displaying the result via the pagination and select dropdown in PHP 8:

// Dynamic limit
$limit = isset($_SESSION['records-limit']) ? $_SESSION['records-limit'] : 5;
// Get total records
$sql = $connection->query("SELECT count(id) AS id FROM authors")->fetchAll();
$allRecrods = $sql[0]['id'];
// Calculate total pages
$totoalPages = ceil($allRecrods / $limit);

Note that, MySQL gives a LIMIT clause that is used to define the number of records to return. The LIMIT clause allows displaying multi-page results via pagination with SQL and is very helpful with large tables.

To get the pagination number, i will define the page parameter later with pagination:

$limit = isset($_SESSION['records-limit']) ? $_SESSION['records-limit'] : 5;
// Current pagination page number
$page = (isset($_GET['page']) && is_numeric($_GET['page']) ) ? $_GET['page'] : 1;
// Offset
$paginationStart = ($page - 1) * $limit;
// Limit query
$authors = $connection->query("SELECT * FROM authors LIMIT $paginationStart, $limit")->fetchAll();

The following code will create the pagination and display the result based on data limitation:

<!-- 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>

Now, Import the jQuery CDN link to get the value from the select dropdown.

Run a loop and pass the values that we want to use for setting up the records limit. We are taking the values from the session and set the same value as a selected:

<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>
<!-- 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>

Conclusion

PHP 8 pagination with bootstrap and jquery example. In this tutorial, you have learned you how to create pagination in php 8 with mysql and boostrap.

Recommended PHP Tutorials

Recommended:- User Registration with Email Verification in PHP
Recommended:- How to Add Captcha in PHP Registration Form
Recommended:- Select Insert Update Delete Record using PHP and MySQL
Recommended:- Insert Update Delete in PHP on Same Page
Recommended:- How to Fetch Data From Database in PHP using Ajax
Recommended:- Get Country, City, latitude, longitude from IP address using PHP
Recommended:- PHP | Implode and Explode Function
Recommended:- Ajax Image Upload Using PHP and jQuery Without Refreshing Page
Recommended:- Send Reset Password Link Email PHP
Crop and Save Image Using jQuery Croppie and PHP
Filed Under: PHP

AuthorDevendra Dode

Greetings, I'm Devendra Dode, a full-stack developer, entrepreneur, and the proud owner of Tutsmake.com. My passion lies in crafting informative tutorials and offering valuable tips to assist fellow developers on their coding journey. Within my content, I cover a spectrum of technologies, including PHP, Python, JavaScript, jQuery, Laravel, Livewire, CodeIgniter, Node.js, Express.js, Vue.js, Angular.js, React.js, MySQL, MongoDB, REST APIs, Windows, XAMPP, Linux, Ubuntu, Amazon AWS, Composer, SEO, WordPress, SSL, and Bootstrap. Whether you're starting out or looking for advanced examples, I provide step-by-step guides and practical demonstrations to make your learning experience seamless. Let's explore the diverse realms of coding together.

Leave a Reply

Your email address will not be published. Required fields are marked *