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:
<?php // 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 = number_format($total / $record_per_page);
Then we can simple 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.
We are sorry that this post was not useful for you!
Let us improve this post!
Tell us how we can improve this post