How to create PHP Pagination using PDO with example?

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

Laravel Pint & VS Code: Automate Your Code Formatting

Laravel Pint is an opinionated PHP code style fixer built on top of PHP-CS-Fixer, designed to simplify the process of ensuring clean and consistent code style in Laravel projects. (...)
Harish Kumar

Best Practices for Testing and Quality Assurance in Custom Web App Development

In the fast-paced world of web app development, delivering high-quality products that meet customer expectations is crucial. But how can you ensure your custom web application (...)

PHP-CS-Fixer: The Ultimate Guide to PHP Code Formatting in VSCode

In this comprehensive guide, we will explore how to use PHP-CS-Fixer in VSCode to automate the process of PHP code formatting and adhere to the best coding practices.
Harish Kumar

Learn PHP Development from beginning.

PHP stance Hypertext Preprocessor, it's server-side scripting laungage and easy to use. PHP  also well  known for its speed, simplicity, flexibility features that have made it (...)
Programmer Desk

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