Hosting Solutions
mobile_logo Table of Contents

Pagination MySQLi Google Style Paged Results Tutorial

Learn to script pagination for PHP and MySQLi based applications. Use pagination programming when your database table has a lot of rows and you do not want all results to render on a single page. Watch the video for line by line explanation of the script and more commentary. <?php // Script and tutorial written by Adam Khoury @ // Line by line explanation : include_once("mysqli_connection.php"); // This first query is just to get the total count of rows $sql = "SELECT COUNT(id) FROM testimonials WHERE approved='1'"; $query = mysqli_query($db_conx, $sql); $row = mysqli_fetch_row($query); // Here we have the total row count $rows = $row[0]; // This is the number of results we want displayed per page $page_rows = 10; // This tells us the page number of our last page $last = ceil($rows/$page_rows); // This makes sure $last cannot be less than 1 if($last < 1){ $last = 1; } // Establish the $pagenum variable $pagenum = 1; // Get pagenum from URL vars if it is present, else it is = 1 if(isset($_GET['pn'])){ $pagenum = preg_replace('#[^0-9]#', '', $_GET['pn']); } // This makes sure the page number isn't below 1, or more than our $last page if ($pagenum < 1) { $pagenum = 1; } else if ($pagenum > $last) { $pagenum = $last; } // This sets the range of rows to query for the chosen $pagenum $limit = 'LIMIT ' .($pagenum - 1) * $page_rows .',' .$page_rows; // This is your query again, it is for grabbing just one page worth of rows by applying $limit $sql = "SELECT id, firstname, lastname, datemade FROM testimonials WHERE approved='1' ORDER BY id DESC $limit"; $query = mysqli_query($db_conx, $sql); // This shows the user what page they are on, and the total number of pages $textline1 = "Testimonials (<b>$rows</b>)"; $textline2 = "Page <b>$pagenum</b> of <b>$last</b>"; // Establish the $paginationCtrls variable $paginationCtrls = ''; // If there is more than 1 page worth of results if($last != 1){ /* First we check if we are on page one. If we are then we don't need a link to the previous page or the first page so we do nothing. If we aren't then we generate links to the first page, and to the previous page. */ if ($pagenum > 1) { $previous = $pagenum - 1; $paginationCtrls .= '<a href="'.$_SERVER['PHP_SELF'].'?pn='.$previous.'">Previous</a> &nbsp; &nbsp; '; // Render clickable number links that should appear on the left of the target page number for($i = $pagenum-4; $i < $pagenum; $i++){ if($i > 0){ $paginationCtrls .= '<a href="'.$_SERVER['PHP_SELF'].'?pn='.$i.'">'.$i.'</a> &nbsp; '; } } } // Render the target page number, but without it being a link $paginationCtrls .= ''.$pagenum.' &nbsp; '; // Render clickable number links that should appear on the right of the target page number for($i = $pagenum+1; $i <= $last; $i++){ $paginationCtrls .= '<a href="'.$_SERVER['PHP_SELF'].'?pn='.$i.'">'.$i.'</a> &nbsp; '; if($i >= $pagenum+4){ break; } } // This does the same as above, only checking if we are on the last page, and then generating the "Next" if ($pagenum != $last) { $next = $pagenum + 1; $paginationCtrls .= ' &nbsp; &nbsp; <a href="'.$_SERVER['PHP_SELF'].'?pn='.$next.'">Next</a> '; } } $list = ''; while($row = mysqli_fetch_array($query, MYSQLI_ASSOC)){ $id = $row["id"]; $firstname = $row["firstname"]; $lastname = $row["lastname"]; $datemade = $row["datemade"]; $datemade = strftime("%b %d, %Y", strtotime($datemade)); $list .= '<p><a href="testimonial.php?id='.$id.'">'.$firstname.' '.$lastname.' Testimonial</a> - Click the link to view this testimonial<br>Written '.$datemade.'</p>'; } // Close your database connection mysqli_close($db_conx); ?> <!DOCTYPE html> <html> <head> <style type="text/css"> body{ font-family:"Trebuchet MS", Arial, Helvetica, sans-serif;} div#pagination_controls{font-size:21px;} div#pagination_controls > a{ color:#06F; } div#pagination_controls > a:visited{ color:#06F; } </style> </head> <body> <div> <h2><?php echo $textline1; ?> Paged</h2> <p><?php echo $textline2; ?></p> <p><?php echo $list; ?></p> <div id="pagination_controls"><?php echo $paginationCtrls; ?></div> </div> </body> </html>

PHP Videos


Website Design Theme Application PHP Cookies CSS TutorialPDO Prepared Statements PHP TutorialPDO Tutorial Connect Database and Query ExampleActive Page Link Button Highlight TutorialGeoPlugin Tutorial Get User Location Information IP DetectionHashtag System and Regex PHP Programming TutorialPagination MySQLi Google Style Paged Results TutorialActive Clickable Link Function preg_replace Programming TutorialGet Favicon From Web Site Link Access External FilesRandom String Generator PHP Function Programming TutorialForce File Download Dialog In Browser TutorialCheck User Sign Up Name Ajax PHP Social Network TutorialFlat File Text Database System CMS Edit Content TutorialFlat File Text Database System Display ContentExperience Level Evaluation Programming TutorialStagger and Limit Dynamic Output in a While Loop Google AdsTwitter Dynamic Goodies Widget TutorialDynamic Google Maps Location TutorialPrepared Statements PHP mysqli Tutorial