Pagination using PHP MySQL
Pagination using PHP MySQL
Pagination using PHP MySQL
, 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. The script i am going to explain below is a very simple method of paginating MySQL query results with the help of PHP.
This 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.
In this script i am using PHP and MySQL along with some CSS to make the paginate area look more appealing.
The very first part of the script is to connect to the database and table. 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.
Here i have a small mysql database with auto increment id column along with other columns namely fname for “First Name”, lname for “Last Name”, address for “Address” and comment for “comment”
I am using mysql_connect(), You can also use mysqli_connect()
//Provide your connection details here
$host = "";
$username = "";
$password = "";
$database = "";
//Make your connection to database
$con = mysql_connect($host,$username,$password);
//Check your connection
if (!$con) {
die("Could not connect: " . mysql_error());
}
//Select your database
$db_selected = mysql_select_db($database, $con);
//Check your connection
if (!$con) {
die("Could not connect: " . mysql_error());
}
//provide your table name here
$table = "";
Now create another php file named paginate.php and add the below set of codes.
include('connect.php');
$rec_limit = 5;
$result_num = mysql_query("SELECT * FROM $table");
$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 = mysql_query($query);
$res_rows_for_last_page=mysql_num_rows($result_num);
$res_rows=mysql_num_rows($result);
$row = mysql_fetch_array($result, MYSQL_NUM );
$num_of_pages= $res_rows_for_last_page / $rec_limit;
$without_decimal=floor($num_of_pages);
$find_last_page = $num_of_pages - $without_decimal;
Here the $rec_limit variable contains the number of rows that you need to display on 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>Address</th><th>Comments</th></tr>";
while($row = mysql_fetch_array($result)) {
echo "<tr class='alt'>";
echo "<td>" . $num . "</td>";
echo "<td><a href='edit/".$row['ID']."'>Edit</a> <p><a href='delete?delete=".$row['ID']."'>Delete</a></td>";
echo "<td>" . $row['fname'] . "</td>";
echo "<td>" . $row['lname'] . "</td>";
echo "<td>" . $row['address'] . "</td>";
echo "<td>" . $row['comment'] . "</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;
Now comes the main part of the pagination script, Here we are checking different scenarios and displaying the pagination accordingly.
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>";
}
Since the coding part is over, lets move to the design phase.
If you want just the pagination area to have some style then just copy paste the CSS below.
.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;
}
If you need the entire webpage, paginate.php designed with the help of CSS then copy the code below.
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;
}
If you find this script useful then please kindly Like us on Facebook 🙂
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.
I will be adding a demo of this script soon 🙂
Pagination using PHP MySQL,
