Last updated on October 21st, 2022 at 09:59 am
It is always a good practice to paginate and split the MySQL query results in to different pages instead of displaying the entire result on a single page. This will help the user to view the results without much effort or confusion. Paginating results also make sure that load to MySQL is minimal. The script I am going to explain below is a very simple method of paginating MySQL query results with the help of PHP.
Script also has an option to jump to different pages directly by just entering the page number. If the page is not found it has the ability to throw an error and then ask the users to use the page numbers accordingly.Adding some CSS to make the paginate area look more appealing.
The very first step in the script is to connect to the database and table.
Let us look closely to the MySQL table we have here. The table name is pagination and the fields available are
+-----------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+----------------+
| ID | int | NO | PRI | NULL | auto_increment |
| FirstName | varchar(255) | NO | | NULL | |
| LastName | varchar(255) | YES | | NULL | |
| Age | int | YES | | NULL | |
+-----------+--------------+------+-----+---------+----------------+
Here is the step by step approach
- Create Connection Strings
- Paginate Using MySQL Limit
- Display Paginate Section
- Add Style CSS
- Complete Code
- Demo
Create Connection Strings
We are going to use mysqli_connect (Supported by newer PHP Versions)
mysql_connect (Old PHP Versions) removed from this tutorial.
First step is to create a file called connect.php and add the below code. Please make sure you provide all the connection details according to your server settings. I will not be explaining every line in the code as the script is easy to understand and doesn’t need any lengthy explanation. If you have any questions please don’t hesitate to comment. Don’t forget to modify the table name as well.
<?php
//Provide your connection details here
$host = "";
$username = "";
$password = "";
$database = "";
$table = "pagination";
//Make your connection to database
$con = mysqli_connect($host,$username,$password);
//Check your connection
if (!$con) {
die("Could not connect: " . mysqli_error());
}
//Select your database
$db_selected = mysqli_select_db($con,$database);
//Check your connection
if (!$con) {
die("Could not connect: " . mysqli_error());
}
else
{
echo "Connected to MySQL, Displaying 4 results per page as per configuration <p>";
}
?>
Paginate And Show Rows
Now create another php file named paginate.php and add the below set of codes.
include('connect.php');
$rec_limit = 5;
$search_details="SELECT * FROM $table";
$result_num = mysqli_query($con,$search_details);
$num=1;
if( isset($_GET{'page'} ) )
{
$page = $_GET{'page'} + 0;
$offset = $rec_limit * $page ;
}
else
{
$page = 0;
$offset = 0;
}
$query="SELECT * FROM $table LIMIT $offset, $rec_limit";
$result = mysqli_query($con,$query);
$res_rows_for_last_page=mysqli_num_rows($result_num);
$res_rows=mysqli_num_rows($result);
$row = mysqli_fetch_array($result);
$num_of_pages= $res_rows_for_last_page / $rec_limit;
$without_decimal=floor($num_of_pages);
$find_last_page = $num_of_pages - $without_decimal;
The $rec_limit variable contains the number of rows that you need to display on each page. Modify this value according to your requirement to limit the number of rows displayed in each page.
The next step is to add the code that will check if the number of rows returned from MySQL is less than 1 or not and then display the output to a HTML table. The isset() part of the script just checks whether the URL has page query string passed in the URL or not, then it adds 2 and 3 to the page number. This is to display the page numbers when the results load.
echo "<p>";
if($res_rows <1)
{echo "<font color=red>No More Data To Display</font><p><input type='button' onclick='javascript:history.go(-1)' value='Go Back'>";
echo "<p>Total pages available are $without_decimal, Please use the page number below $without_decimal";
}
else{
echo "<table id='users'>
<tr>
<th>NO#</th><th>Modify</th><th>First Name</th><th>Last Name</th><th>Age</th></tr>";
while($row = mysqli_fetch_array($result)) {
echo "<tr class='alt'>";
echo "<td>" . $num . "</td>";?>
<td><a href='#' onClick="alert('Process Something for <?php echo $row['FirstName']; ?>')">Click Here</a></td>
<?php echo "<td>" . $row['FirstName'] . "</td>";
echo "<td>" . $row['LastName'] . "</td>";
echo "<td>" . $row['Age'] . "</td>";
echo "</tr>";
$num++;
}
echo "</table><div class='pagination clearfix'>";
if(!isset($_GET['page']))
{
$page=0;
}
else
{$page = $_GET{'page'};
}
$count_page1 = $page + 2;
$count_page2 = $page + 3;
Display Paginate Section
Next comes the important logic of the pagination script, Here we are checking different scenarios and displaying the pagination accordingly. This is part of the paginate.php script.
if( ($page > 0) && ($res_rows == $rec_limit) )
{
$last = $page - 1;
echo "<a style='text-decoration: none' href=paginate.php?page=".$last."><< Previous</a> ";
$page = $_GET{'page'} + 1;
echo "<a style='text-decoration: none' href=paginate.php?page=".$page.">Go To Page $page >></a>";
echo "<a style='text-decoration: none' href=paginate.php?page=".$without_decimal."> Last Page >></a>";
}
else if( $page == 0 & $without_decimal > 3 )
{
echo "<a style='text-decoration: none' href=paginate.php?page=1> 1 </a>";
echo "<a style='text-decoration: none' href=paginate.php?page=".$count_page1."> ".$count_page1." </a>";
echo "<a style='text-decoration: none' href=paginate.php?page=".$count_page2."> ".$count_page2." </a>";
echo "<a style='text-decoration: none' href=paginate.php?page=".$without_decimal."> Last Page >></a>";
}
else if( $page == 0 & $without_decimal < 3 )
{
echo "<a style='text-decoration: none' href=paginate.php?page=1>Go To Page 1 </a>";
echo "<a style='text-decoration: none' href=paginate.php?page=".$without_decimal."> Last Page >></a>";
}
else if( $res_rows < $rec_limit )
{
$last = $page - 1;
echo "<a style='text-decoration: none' href=paginate.php?page=".$last."><< Previous</a>";
}
echo "<p>Jump To Page <form action='paginate.php' post='get'><input type='text' size='3' name='page'><input type='submit' value='Go'></form></div>";
}
Add style using CSS
Since the coding part is over, let us move to the design phase.
If you want just the pagination area to have some style then just copy paste the CSS below. Add the below CSS code inside the <head> tag. If you need the entire webpage, paginate.php designed with the help of CSS then copy the code below.
<head>
<style>
body{
margin-top: 60px;
padding:0;
font-family:'Droid Sans',sans-serif
}
#users {
font-family: "Trebuchet MS", Arial, Helvetica, sans-serif;
width: 100%;
border-collapse: collapse;
}
#users td, #users th {
font-size: 1em;
border: 1px solid #98bf21;
padding: 3px 7px 2px 7px;
}
#users th {
font-size: 1.1em;
text-align: left;
padding-top: 5px;
padding-bottom: 4px;
background-color: #A7C942;
color: #ffffff;
}
#users tr.alt td {
color: #000000;
background-color: #EAF2D3;
}
#users tr:hover td{
font-weight: bold;
background-color: #ffff99;color: #000;;
}
.pagination {
text-align: center;
margin: 20px
}
.pagination a, .pagination strong {
background: #EFEFEF;
display: inline-block;
margin-right: 3px;
padding: 4px 12px;
text-decoration: none;
line-height: 1.5em;
-webkit-border-radius: 3px;
-moz-border-radius: 3px;
border-radius: 3px;
}
.pagination a:hover {
background-color: #BEBEBE;
color: #fff;
}
.pagination a:active {
background: rgba(190, 190, 190, 0.75);
}
.pagination strong {
color: #fff;
background-color: #BEBEBE;
}
</style>
</head>
Complete Code
For you convenience I am adding the complete paginate.php code below (same code used for our demo)
<body>
<?php
include('connect.php');
$rec_limit = 5;
$search_details="SELECT * FROM $table";
$result_num = mysqli_query($con,$search_details);
$num=1;
if( isset($_GET{'page'} ) )
{
$page = $_GET{'page'} + 0;
$offset = $rec_limit * $page ;
}
else
{
$page = 0;
$offset = 0;
}
$query="SELECT * FROM $table LIMIT $offset, $rec_limit";
$result = mysqli_query($con,$query);
$res_rows_for_last_page=mysqli_num_rows($result_num);
$res_rows=mysqli_num_rows($result);
$row = mysqli_fetch_array($result);
$num_of_pages= $res_rows_for_last_page / $rec_limit;
$without_decimal=floor($num_of_pages);
$find_last_page = $num_of_pages - $without_decimal;
echo "<p>";
if($res_rows <1)
{echo "<font color=red>No More Data To Display</font><p><input type='button' onclick='javascript:history.go(-1)' value='Go Back'>";
echo "<p>Total pages available are $without_decimal, Please use the page number below $without_decimal";
}
else{
echo "<table id='users'>
<tr>
<th>NO#</th><th>Modify</th><th>First Name</th><th>Last Name</th><th>Age</th></tr>";
while($row = mysqli_fetch_array($result)) {
echo "<tr class='alt'>";
echo "<td>" . $num . "</td>";?>
<td><a href='#' onClick="alert('Process Something for <?php echo $row['FirstName']; ?>')">Click Here</a></td>
<?php echo "<td>" . $row['FirstName'] . "</td>";
echo "<td>" . $row['LastName'] . "</td>";
echo "<td>" . $row['Age'] . "</td>";
echo "</tr>";
$num++;
}
echo "</table><div class='pagination clearfix'>";
if(!isset($_GET['page']))
{
$page=0;
}
else
{$page = $_GET{'page'};
}
$count_page1 = $page + 2;
$count_page2 = $page + 3;
if( ($page > 0) && ($res_rows == $rec_limit) )
{
$last = $page - 1;
echo "<a style='text-decoration: none' href=paginate.php?page=".$last."><< Previous</a> ";
$page = $_GET{'page'} + 1;
echo "<a style='text-decoration: none' href=paginate.php?page=".$page.">Go To Page $page >></a>";
echo "<a style='text-decoration: none' href=paginate.php?page=".$without_decimal."> Last Page >></a>";
}
else if( $page == 0 & $without_decimal > 3 )
{
echo "<a style='text-decoration: none' href=paginate.php?page=1> 1 </a>";
echo "<a style='text-decoration: none' href=paginate.php?page=".$count_page1."> ".$count_page1." </a>";
echo "<a style='text-decoration: none' href=paginate.php?page=".$count_page2."> ".$count_page2." </a>";
echo "<a style='text-decoration: none' href=paginate.php?page=".$without_decimal."> Last Page >></a>";
}
else if( $page == 0 & $without_decimal < 3 )
{
echo "<a style='text-decoration: none' href=paginate.php?page=1>Go To Page 1 </a>";
echo "<a style='text-decoration: none' href=paginate.php?page=".$without_decimal."> Last Page >></a>";
}
else if( $res_rows < $rec_limit )
{
$last = $page - 1;
echo "<a style='text-decoration: none' href=paginate.php?page=".$last."><< Previous</a>";
}
echo "<p>Jump To Page <form action='paginate.php' post='get'><input type='text' size='3' name='page'><input type='submit' value='Go'></form></div>";
}
?>
</body>
Hope this script has given you an idea on how to implement pagination using PHP and MySQL. You are free to modify this code according to your needs.
admin