Last updated on November 6th, 2024 at 12:19 pm
It is advisable to implement pagination when displaying MySQL query results, rather than presenting all results on a single page. This approach enhances user experience by allowing for easier navigation and comprehension of the data. Additionally, pagination reduces the load on the MySQL server. The script I will describe below provides a straightforward method for paginating MySQL query results using PHP.
This script includes a feature that enables users to navigate directly to specific pages by entering the desired page number. In the event that a page does not exist, the script is designed to generate an error message, prompting users to enter valid page numbers. Furthermore, incorporating some CSS can enhance the visual appeal of the pagination area.
The initial step in the script involves establishing a connection to the database and the relevant 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.