Contents:
1.0 Introduction
2.0 Video Demo
4.0 Complete Code
1.0 Introduction
PHP Pagination is another very important feature when building any web application. 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.
2.0 Video Demo
Our final output.
3.0 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:
3.1 Retrieve the Records (Step 1)
We will show the records in a 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 ***** }
3.2 First and Previous Page Buttons (Step 2)
This is the buttons with signs "<<" and "<".
<?php // ***** 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>"; } ?>
3.3 Number Page Buttons (Step 3)
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> "; } } } ?>
3.4 Next and Last Page Buttons (Step 4)
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>"; } ?>
3.5 Enter a Page Number (Step 5)
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>"; ?>
4.0 Complete Code
Here's the complete code to enjoy:
<!DOCTYPE HTML> <html> <head> <title>PHP Paging Tutorial Demo</title> <link rel="stylesheet" type="text/css" href="css/style.css"> </head> <body> <?php // include 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> *************** echo "<div id='paging'>"; // ***** 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>"; } // ********** 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> "; } } } // ***** 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>"; } echo "</div>"; // ***** 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>"; // *************** </PAGING_SECTION> *************** } // tell the user if no records were found else{ echo "<div class='noneFound'>No records found.</div>"; } ?> </body> </html>
I hope you enjoy this simple PHP Paging code!
For FREE programming tutorials, click the red button below and subscribe! :)
website