Saturday, December 28, 2024
Google search engine
HomeLanguagesPagination in PHP MySQL with Next and Previous Example
LanguagesPhp

Pagination in PHP MySQL with Next and Previous Example

Dominic Rubhabha-Wardslaus
By Dominic Rubhabha-Wardslaus
0
2

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

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
Previous article
Install PHP 8 on Ubuntu 22.04
Next article
Find Number of Months & Days Between Two Dates in PHP
Dominic Rubhabha-Wardslaus
Dominic Rubhabha-Wardslaushttp://wardslaus.com
infosec,malicious & dos attacks generator, boot rom exploit philanthropist , wild hacker , game developer,
RELATED ARTICLES

Most Popular

Load more

Recent Comments

강서구출장마사지 on How to store XML data into a MySQL database using Python?
금천구출장마사지 on How to store XML data into a MySQL database using Python?
nightwish.southeast.cz on Google says it won’t keep your Pixel during a repair if you’re caught using non-OEM parts
광명출장안마 on How to store XML data into a MySQL database using Python?
광명출장안마 on How to store XML data into a MySQL database using Python?
출장오피 on How to store XML data into a MySQL database using Python?
부천출장안마 on How to store XML data into a MySQL database using Python?
구월동출장안마 on How to store XML data into a MySQL database using Python?
강서구출장안마 on How to store XML data into a MySQL database using Python?
헬로출장 on How to store XML data into a MySQL database using Python?
오산출장안마 on How to store XML data into a MySQL database using Python?
광명출장마사지 on How to store XML data into a MySQL database using Python?
마포출장 on How to store XML data into a MySQL database using Python?
안양출장마사지 on How to store XML data into a MySQL database using Python?
gKTdhA on 5 reasons why I won’t switch away from Google Photos
부천출장안마 on How to store XML data into a MySQL database using Python?
동탄출장안마 on How to store XML data into a MySQL database using Python?
0a1Mq7 on Wander: An add-on for Apple’s Shortcuts app to install the Odyssey jailbreak
서울출장안마 on How to store XML data into a MySQL database using Python?
분당출장안마 on How to store XML data into a MySQL database using Python?
부천출장안마 on How to store XML data into a MySQL database using Python?
출장 오피 on How to store XML data into a MySQL database using Python?
화곡동출장마사지 on How to store XML data into a MySQL database using Python?
Gilda on Wander: An add-on for Apple’s Shortcuts app to install the Odyssey jailbreak
강서구출장마사지 on How to store XML data into a MySQL database using Python?
고양출장안마 on How to store XML data into a MySQL database using Python?
화성출장마사지 on How to store XML data into a MySQL database using Python?
천호동출장마사지 on How to store XML data into a MySQL database using Python?
June P. D. Alvarez on RedSn0w Updated to Fix iBooks DRM Issues
Litha on How to Install Siri on iPad 2

EDITOR PICKS

POPULAR POSTS

POPULAR CATEGORY

ABOUT US

Newspaper is your news, entertainment, music fashion website. We provide you with the latest breaking news and videos straight from the entertainment industry.

Contact us: hello@geeksforgeeks.org

FOLLOW US

© Lazyr0ar 2022