How to create PHP Pagination using PDO with example?

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

NetSuite ERP vs. Microsoft Dynamics: Which is the Winner?

Businesses frequently encounter a dilemma when deciding which enterprise resource planning (ERP) solution best suits their varied operational requirements. Two prominent contenders (...)
EPIQ Infotech

Data Integration Tools

An ocean of data integration tools that promise to be “the best” makes it easy to get confused. Based on research, usage experience, and popular ratings, we have compiled a (...)
Narola Infotech

What's New in PHP 8.3? Your Guide to the Latest Features and Enhancements

PHP, the popular scripting language, continues its evolution with the major release of PHP 8.3. This update, though categorized as a minor release, brings a plethora of new features (...)
Harish Kumar

A Comprehensive Guide to #[Override] Attribute in PHP 8.3

PHP 8.3 has ushered in an array of advanced features, among which the  #[Override] attribute stands out. This attribute, while known in other languages, is a fresh addition to (...)
Harish Kumar

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 (...)
VisionX Technologies