How to create PHP Pagination using PDO with example?

Razet · · 4767 Views

In this example, I am going to show you how to create pagination in PHP using PDO.First, create a DatabaseConnection.php to create a database connection.

<?php

class DatabaseConnection
{
    private $server = 'mysql:host=localhost;dbname=database_name';

    private $user = 'root';

    private $pass = 'password';

    private $options = [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    ];

    protected $con;

    public function openConnection()
    {
        try {
            $this->con = new PDO($this->server, $this->user, $this->pass, $this->options);

            return $this->con;
        } catch (PDOException $e) {
            echo 'There is some problem in connection: ' . $e->getMessage();
        }
    }

    public function closeConnection()
    {
        $this->con = null;
    }
}

Here is the simple snippet to create pagenation:

<?php
    // Database connection
    include_once 'DatabaseConnection.php';
    $database = new DatabaseConnection();
    $db = $database->openConnection();

    $perPage = 10;

    // Calculate Total pages
    $stmt = $db->query('SELECT count(*) FROM users');
    $total_results = $stmt->fetchColumn();
    $total_pages = ceil($total_results / $perPage);

    // Current page
    $page = isset($_GET['page']) ? $_GET['page'] : 1;
    $starting_limit = ($page - 1) * $perPage;

    // Query to fetch users
    $query = "SELECT * FROM users ORDER BY id DESC LIMIT $starting_limit,$perPage";

    // Fetch all users for current page
    $users = $db->query($query)->fetchAll();

?>

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

<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Pagination</title>
</head>

<body>
    <?php foreach ($users as $key => $user): ?>
        <h4><?php echo $user['id'];?></h4>
        <p><?php echo $user['name'];?></p>
        <hr>
    <?php endforeach; ?>


    <?php for ($page = 1; $page <= $total_pages ; $page++):?>
        <a href='<?php echo "?page=$page"; ?>' class="links">
            <?php  echo $page; ?>
        </a>
    <?php endfor; ?>
</body>

</html>
0

Please login or create new account to add your comment.

0 comments
You may also like:

What is the difference between classes vs enums in PHP 8.1?

One of the common questions that I have been asked is what is the difference between classes and enums. Class(es) and enum(s) share many similarities but differ in some aspects. (...)
Harish Kumar

How to use the enumerations(Enums) of PHP 8.1 in Laravel?

The release of PHP 8.1 brings native enumerations to PHP. There is no more requirement for custom solutions in your Laravel projects since the Laravel v8.69 release has you back. (...)
Harish Kumar

What is Enumerations(Enums) in PHP 8.1? Enums in PHP v8.1 Explained in detail with examples.

PHP 8.1 added many new features, and Enumerations (Enum) is our favourite new feature. Enumerations (or enums for short) allow us to define a new structure similar to a class however (...)
Harish Kumar

Web App Development Technologies

If you have been planning to create robust websites or web apps, you should be aware of the various technologies required to make it happen. Over time, these technologies have (...)
Narola Infotech

Install phpMyAdmin Manually with Nginx server on Ubuntu

In this guide, I will show you how to install and configure phpMyAdmin with Nginx, MySQL, and PHP8.0 (LEMP) on an Ubuntu system. phpMyAdmin is a free and open-source database (...)
Harish Kumar

How to Upload Multiple Images with jQuery AJAX and PHP, with preview

Uploading an image without page refresh is more user-friendly than refreshing the entire page. So, in this guide, you will learn how to upload multiple images using ajax in jQuery (...)
Nakul Kumar