Pagination in PHP MySQL with Next and Previous Example

If you are displaying data in a list in PHP with MySQL using pagination. And you want to show only 10 data on one page of these list. And also you want to put next and previous buttons. So you can easily show your data in list.

Dynamic pagination in php with mysql example; In this tutorial, you will learn you how to create dynamic pagination in php and mysql with next and previous button. And also learn how to use Bootstrap to create pagination buttons with next and previous.

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