Dynamic Pagination – PHP

Dynamic pagination is one of the most amazing way to optimize your page load if you have a lot of record in your database. You can see it in a tech giant Google, when you search for something, you will see a list of page numbers at the bottom of page. We will be using a sample database called classicmodels and we are assuming to display 5 records in one page.

Creating a layout

Our layout should be super simple, a simple bootstrap table and an unordered list for page number list:

<table class="table">
    <tr>
        <th>Employee number</th>
        <th>First name</th>
        <th>Last name</th>
        <th>Email</th>
    </tr>
</table>

Displaying dynamic pagination links

To display pagination links, we have to divide total rows by the number of records we want to display in 1 page. For example, if you have 30 records in your database and you want to display 5 records in 1 page, the formula should be:

30 / 5 = 6

Thus, 6 links will be displayed in an unordered list:

// Connecting with database
$connection = mysqli_connect("localhost", "root", "", "classicmodels");
// How many records will be displayed in one page
$record_per_page = 5;
// Getting total number of records
$sql = "SELECT COUNT(*) AS total FROM employees";
$result = mysqli_query($connection, $sql);
$total = mysqli_fetch_object($result)->total;
// Calculating number of pagination links required
$pages = ceil($total / $record_per_page);

Then we can simply loop till $pages variable and display the list items:

<ul class="pagination">
    <?php for ($a = 1; $a <= $pages; $a++): ?>
        <li class="<?php echo $a == $page_number ? 'active' : ''; ?>">
            <a href="?page=<?php echo $a; ?>">
                <?php echo $a; ?>
            </a>
        </li>
    <?php endfor; ?>
</ul>

At this point, if you run the file you will only see pagination links. On clicking you will see $page variable in the URL. We will be using this to display records as per that page.

Displaying records based on page number

Before the <table> tag, you need to check if there is $page variable in the URL. If not, then the variable should have value 1, by default. To append that variable in MySQL query, we need to subtract 1 from it as the index starts from 0. Then we will multiply it with 5 (number of records in 1 page). So your basic formula should be:

($page_number – 1) * 5

So if you are on page 3, then putting the values you will get:

(3 – 1) * 5 = 10

Thus, it will get records from 10 to 15. Below is the code for it:

$page_number = isset($_GET["page"]) ? $_GET["page"] : 1;
$start_from = ($page_number - 1) * $record_per_page;
$sql = "SELECT * FROM employees LIMIT " . $start_from . ", " . $record_per_page;
$result = mysqli_query($connection, $sql);

And to display it in <table> you can do:

<?php while ($row = mysqli_fetch_object($result)): ?>
    <tr>
        <td><?php echo $row->employeeNumber; ?></td>
        <td><?php echo $row->firstName; ?></td>
        <td><?php echo $row->lastName; ?></td>
        <td><?php echo $row->email; ?></td>
    </tr>
<?php endwhile; ?>

That’s it. You can also learn dynamic pagination in Node JS and Mongo DB from here.

[wpdm_package id=’140′]