PHP-How-To-Work-with-Data-Pagination-Tutorial

PHP How To Work with Data Pagination Tutorial

by
Reading Time: 8 minutes
1,263 Views

Inside this article we will discuss about the concept i.e PHP How To Work with Data Pagination Tutorial. Article contains the classified information about getting pagination links of mysql data. This article doesn’t contains any use of third party plugin like DataTable jQuery plugin.

Article use the core concept of PHP, MySQL Database, PDO and Bootstrap to render table and their pagination links. You can use this concept when you want to display a bulk list of data into your application.

If you are looking for an article which gives you the concept to create data pagination using PHP and PDO then this article will help you a lot to understand the things in a very clear way.

Learn More –

Let’s get started.

Create Database & Table

To create a database, either we can create via Manual tool of PhpMyadmin or by means of a mysql command.

CREATE DATABASE php_applications;

Inside this database, we need to create a table.

Table we need – authors

CREATE TABLE `authors` (
 `id` int(11) NOT NULL 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,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Next, you need to insert some test data into it. Test data you can get from here.

Table with data will look like this –

Application Folder Structure

You need to create a folder structure to develop this application in PHP and PDO. Have a look the files inside this application –

Create a folder with name php-data-pagination and create these 2 files into it.

Database Configuration

Open dbconfig.php file from folder. Add these lines of code into it.

<?php
/*
 @Author: Sanjay Kumar
 @Project: PHP How To Work with Data Pagination Tutorial
 @Email: onlinewebtutorhub@gmail.com
 @Website: https://onlinewebtutorblog.com/
*/
$hostname = "localhost";
$username = "admin";
$password = "Admin@123";

try {

    $connection = new PDO("mysql:host=$hostname;dbname=php_applications", $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();
}

Application Programming

Open index.php file and write this following code into it. 

<?php
/*
 @Author: Sanjay Kumar
 @Project: PHP How To Work with Data Pagination Tutorial
 @Email: onlinewebtutorhub@gmail.com
 @Website: https://onlinewebtutorblog.com/
*/

// Database
include_once 'dbconfig.php';
// Set session
session_start();
if (isset($_POST['records-limit'])) {
    $_SESSION['records-limit'] = $_POST['records-limit'];
}
$limit = isset($_SESSION['records-limit']) ? $_SESSION['records-limit'] : 10;
$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-2">
        <h3 class="text-center mb-2">PHP How To Work with Data Pagination Tutorial</h3>
        <!-- Select dropdown -->
        <div class="d-flex flex-row-reverse bd-highlight mb-2">
            <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-2">
            <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>

Application Testing

Now,

Open into browser.

URL: http://localhost/php-data-pagination/index.php

Download Complete Source Code

Download Source Code

We hope this article helped you to learn PHP How To Work with Data Pagination Tutorial in a very detailed way.

Online Web Tutor invites you to try Skillshike! Learn CakePHP, Laravel, CodeIgniter, Node Js, MySQL, Authentication, RESTful Web Services, etc into a depth level. Master the Coding Skills to Become an Expert in PHP Web Development. So, Search your favourite course and enroll now.

If you liked this article, then please subscribe to our YouTube Channel for PHP & it’s framework, WordPress, Node Js video tutorials. You can also find us on Twitter and Facebook.