Overtime, web development is becoming diverse with various tricks coming into place to replace the older methods of programming. With the use of the database to display dynamic data depending on the request made by the user or showing data that is user-specific, pagination had to be reinvented such that since the number of results displayed may vary depending on the query, you cannot manually define the pagination by yourself.
That’s why in this article we shall discuss how to create pagination using PHP and MySQL. To get the page that the user is on, we use the GET method to get the components in the URL address.
While creating pagination, the important thing you should understand is the number of records you are displaying on a single page and this must be standard, that is, if you decide that after ten records or twenty records you add a new page, it should be that way consistently till all data is displayed. It’s only the last page of the pagination that seems to have fewer records if the data displayed is less than the limit defined.
To begin on creating pagination,
First, you need to have data in a database table. For this article, we will use a table called users which has 34 records.
Next, we will define our limit as ten that is for every page we display ten records meaning with 34 entries we will have a 4 pagination result.
Next, you define the variable that will hold the current page in the URL address bar and for this example we will use the page variable
https://www.solutionspacenet.com/?page=2
The above URL shows how the address bar appears when the page defined is 2
Next, we need to define a start from a variable which will determine where the data we are displaying from the database will start, that is, for example on page 2, the data to display cannot be from one since we already have records 1 to 10 already shown in page 1. The start from variable gets its data from multiplying the limit with the current page we are in minus 1
$start_from = ($page-1) * $limit;
To get the page that we are in currently, from the URL displayed above, we use the below code
if (isset($_GET["page"])) {
$page = $_GET["page"];
} else {
$page=1;
};
If the page is not defined in the address bar, which is the case with page 1 since it is displayed when we load the page, it is assigned as page 1.
To display data from the database table users using the limit and start from variables, the code snippet is as below
<?php
$limit = 10;
if (isset($_GET["page"])) {
$page = $_GET["page"];
} else {
$page=1;
};
$start_from = ($page-1) * $limit;
$listposts=mysqli_query($con,"SELECT * FROM users LIMIT $start_from, $limit");
?>
The data is fetched from the database depending on the limit defined and the current page in the browser.
Next, we now have to create the pagination at the bottom of the page which are the ones that are to be clicked to move to the next page or any other page in the file
To do this,
First we need to get the number of records in the database table
$sql = "SELECT count(*) FROM users";
$rs_result = mysqli_query($con, $sql);
$row = mysqli_fetch_row($rs_result);
$total_records = $row[0];
Next, you determine the number of pages that will be displayed which is obtained after dividing the total records with the limit and then rounding the result to the next whole number using ceil function in PHP
$total_pages = ceil($total_records / $limit);
Finally you will need to display the pages using the for each loop
$pagLink = "<nav><ul class='pagination'>";
for ($i=1; $i<=$total_pages; $i++) {
if($i == 1 && $page == 1) {
$pagLink .= "<li class='active'><a href='/?page=".$i."'>".$i."</a></li>";
}else if($i != 1 && $i == $page) {
$pagLink .= "<li class='active'><a href='/?page=".$i."'>".$i."</a></li>";
}
else{
$pagLink .= "<li><a href='/?page=".$i."'>".$i."</a></li>";
}
};
echo $pagLink . "</ul></nav>";
The full code for displaying the pagination links using PHP and MySQL is as shown below
<?php
$sql = "SELECT count(*) FROM users";
$rs_result = mysqli_query($con, $sql);
$row = mysqli_fetch_row($rs_result);
$total_records = $row[0];
$total_pages = ceil($total_records / $limit);
$pagLink = "<nav><ul class='pagination'>";
for ($i=1; $i<=$total_pages; $i++) {
if($i == 1 && $page == 1) {
$pagLink .= "<li class='active'><a href='/?page=".$i."'>".$i."</a></li>";
}else if($i != 1 && $i == $page) {
$pagLink .= "<li class='active'><a href='/?page=".$i."'>".$i."</a></li>";
}
else{
$pagLink .= "<li><a href='/?page=".$i."'>".$i."</a></li>";
}
};
echo $pagLink . "</ul></nav>";
?>
Conclusion
Please note that you should include the bootstrap and jquery files for styling to happen. That’s all for this article where we have covered how to create pagination using PHP and MySQL. Ensure you practice with data that is large and also change the limit and observe your results.
