How to create PHP Pagination using PDO with example?

Razet · · 178 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:

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

How to Add Google reCAPTCHA to a PHP Form with Example?

In this guide, we will integrate the Google reCAPTCHA in the PHP contact form. Utilizing this Google reCAPTCHA code, we can validate the human users and protect the form of submission (...)
Nakul Kumar

How to create Stored Procedures in PostgreSQL with example?

In this post, you will learn how to use the CREATE PROCEDURE statement to create new stored procedures in PostgreSQL.
Sumit Talwar

What's new in PHP 8: New Features and Improvements

PHP 8.0 is a major update of the PHP language. It contains a range of new features and optimizations such as the JIT compiler, union types, attributes, constructor property promotion, (...)
Harish Kumar

How To Install LEMP stack (Linux, Nginx, MySQL, PHP) on Ubuntu System

The term LEMP is an acronym of the four open-source projects:
Harish Kumar