Simple PHP Pagination Script to Handle Your Growing Data
When you grow your data, it must not be displayed on a single page – it can cause a browser hang or very long vertical scroll-bar, so in short, it is not good for the user experience.
Video Demo
Our final output.
Pagination in 5 Easy Steps
We are going the build this PHP paging script, in just 5 easy steps, see 3.1 to 3.5 below:
Retrieve the Records
This code will connect us to a MySQL database. Create "libs" folder. Open that folder. Create "db_connect.php" file and place the following code.
<?php
$host = "CHANGE_TO_YOUR_HOST";
$db_name = "CHANGE_TO_YOUR_DATABASE_NAME";
$username = "CHANGE_TO_YOUR_DATABASE_USERNAME";
$password = "CHANGE_TO_YOUR_DATABASE_PASSWORD";
try {
$con = new PDO("mysql:host={$host};dbname={$db_name}", $username, $password);
}
//to handle connection error
catch(PDOException $exception){
echo "Connection error: " . $exception->getMessage();
}
?>Next, we will show the records in an HTML table.
<?php
// include for database connection
include 'libs/db_connect.php';
// page is the current page, if there's nothing set, default is page 1
$page = isset($_GET['page']) ? $_GET['page'] : 1;
// set records or rows of data per page
$recordsPerPage = 3;
// calculate for the query LIMIT clause
$fromRecordNum = ($recordsPerPage * $page) - $recordsPerPage;
// select all data
$query = "SELECT
id, firstname, lastname, username
FROM
users
ORDER BY
id desc
LIMIT
{$fromRecordNum}, {$recordsPerPage}";
/*
page and its LIMIT clause looks like:
1 = 0, 5
2 = 5,10
3 = 10,15
4 = 15, 20
5 = 20, 25
*/
$stmt = $con->prepare( $query );
$stmt->execute();
//this is how to get number of rows returned
$num = $stmt->rowCount();
//check if more than 0 record found
if($num>0){
//start table
echo "<table id='tfhover' class='tftable' border='1'>";
//creating our table heading
echo "<tr>";
echo "<th>Firstname</th>";
echo "<th>Lastname</th>";
echo "<th>Username</th>";
echo "</tr>";
//retrieve our table contents
//fetch() is faster than fetchAll()
//http://stackoverflow.com/questions/2770630/pdofetchall-vs-pdofetch-in-a-loop
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)){
//extract row, this will make $row['firstname'] to just $firstname only
extract($row);
//creating new table row per record
echo "<tr>";
echo "<td>{$firstname}</td>";
echo "<td>{$lastname}</td>";
echo "<td>{$username}</td>";
echo "</tr>";
}
echo "</table>";//end table
// ***** Paging section will be here *****
}First and Previous Page Buttons
The code below will show the "First" and "Previous" page buttons.
// ***** for 'first' and 'previous' pages
if($page>1){
// ********** show the first page
echo "<a href='" . $_SERVER['PHP_SELF'] . "' title='Go to the first page.' class='customBtn'>";
echo "<span style='margin:0 .5em;'> << </span>";
echo "</a>";
// ********** show the previous page
$prev_page = $page - 1;
echo "<a href='" . $_SERVER['PHP_SELF']
. "?page={$prev_page}' title='Previous page is {$prev_page}.' class='customBtn'>";
echo "<span style='margin:0 .5em;'> < </span>";
echo "</a>";
}
?>Number Page Buttons
We are going to have some simple calculations here. The goal of this code is to display some page numbers that users can click.
<?php
// ********** show the number paging
// find out total pages
$query = "SELECT COUNT(*) as total_rows FROM users";
$stmt = $con->prepare( $query );
$stmt->execute();
$row = $stmt->fetch(PDO::FETCH_ASSOC);
$total_rows = $row['total_rows'];
$total_pages = ceil($total_rows / $recordsPerPage);
// range of num links to show
$range = 2;
// display links to 'range of pages' around 'current page'
$initial_num = $page - $range;
$condition_limit_num = ($page + $range) + 1;
for ($x=$initial_num; $x<$condition_limit_num; $x++) {
// be sure '$x is greater than 0' AND 'less than or equal to the $total_pages'
if (($x > 0) && ($x <= $total_pages)) {
// current page
if ($x == $page) {
echo "<span class='customBtn' style='background:red;'>$x</span>";
}
// not current page
else {
echo " <a href='{$_SERVER['PHP_SELF']}?page=$x' class='customBtn'>$x</a> ";
}
}
}
?>Next and Last Page Buttons
This will show the buttons with characters “>” and “>>” (meaning ‘next’ and ‘last’)
<?php
// ***** for 'next' and 'last' pages
if($page<$total_pages){
// ********** show the next page
$next_page = $page + 1;
echo "<a href='" . $_SERVER['PHP_SELF'] . "?page={$next_page}' title='Next page is {$next_page}.' class='customBtn'>";
echo "<span style='margin:0 .5em;'> > </span>";
echo "</a>";
// ********** show the last page
echo "<a href='" . $_SERVER['PHP_SELF'] . "?page={$total_pages}' title='Last page is {$total_pages}.' class='customBtn'>";
echo "<span style='margin:0 .5em;'> >> </span>";
echo "</a>";
}
?>Enter a Page Number
This will show a text-box where the user can enter a page number and submit it by hitting the enter key or clicking the ‘Go’ button.
<?php
// ***** allow user to enter page number
echo "<form action='" . $_SERVER['PHP_SELF'] . "' method='GET'>";
echo "Go to page: ";
echo "<input type='text' name='page' size='1' />";
echo "<input type='submit' value='Go' class='customBtn' />";
echo "</form>";
?>Download Source Code Examples
You can download all the code used in this tutorial for only $9.99 $5.55!
[purchase_link id="12382" text="Download Now" style="button" color="green"]
If you want to paginate with jQuery, here’s a post I made: Pagination with jQuery and PHP
Thank you for learning from our post about: Simple PHP Pagination Script!