Pagination of MySQL Query Results

01
of 02

Setting the Variables

As your database grows, showing all the results of a query on a single page is no longer practical. This is where pagination in PHP and MySQL comes in handy. You can display the results over a number of pages, each linked to the next, to allow your users to browse the content on your website in bite-sized pieces.

The code below first connects to the database. Then you need to know which page of results to display. The if (!(isset($pagenum))) code checks if the page number ($pagenum) isn't set, and if so, sets it to 1. If there is a page number already set, this code is ignored.

You run the query. The $data line should be edited to apply to your site and to return what you need to count results. The $rows line then simply counts the number of results for your query.

Next, you define $page_rows, which is the number of results you want to display on each page before moving to the next page of results. You can then calculate the total number of pages you have ($last) by dividing the total amount of results (rows) by the number of results you want per page. Use CEIL here to round all numbers up to the next whole number.

Next, the code runs a check to make sure the page number is valid. If the number is less than one or greater than the total number of pages, it resets to the closest page number with content.

Finally, you set the range ($max ) for the results using the LIMIT function. The starting number is determined by multiplying the results per page by one less than the current page. The duration is the number of results that display per page.

Code for Setting Pagination Variables

 <?php 

 // Connects to your Database 

 mysql_connect("your.hostaddress.com", "username", "password") or die(mysql_error()); 

 mysql_select_db("address") or die(mysql_error()); 

  //This checks to see if there is a page number. If not, it will set it to page 1 

 if (!(isset($pagenum))) 

 { 

 $pagenum = 1; 

 } 

 //Here we count the number of results 

 //Edit $data to be your query 

 $data = mysql_query("SELECT * FROM topsites") or die(mysql_error()); 

 $rows = mysql_num_rows($data); 

 //This is the number of results displayed per page 

 $page_rows = 4; 

 //This tells us the page number of our last page 

 $last = ceil($rows/$page_rows); 

 //this makes sure the page number isn't below one, or more than our maximum pages 

 if ($pagenum < 1) 

 { 

 $pagenum = 1; 

 } 

 elseif ($pagenum > $last) 

 { 

 $pagenum = $last; 

 }  

 //This sets the range to display in our query 

 $max = 'limit ' .($pagenum - 1) * $page_rows .',' .$page_rows; 

02
of 02

Query and Results

This code reruns the query from earlier, only with one slight change. This time it includes the $max variable to limit the query results to those that belong on the current page. After the query, you display the results as normal using any formatting you wish.

When the results are displayed, the current page is shown along with the total number of pages that exist. This is not necessary, but it is nice information to know.

Next, the code generates the navigation. The assumption is that if you are on the first page, you don't need a link to the first page. As it is the first result, no previous page exists. So the code checks (if ($pagenum == 1) ) to see if the visitor is on page one. If so, then nothing happens. If not, then PHP_SELF and the page numbers generate links to both the first page​and the previous page.

You do almost the same thing to generate the links on the other side. However, this time you are checking to make sure you aren't on the last page. If you are, then you don't need a link to the last page, nor does a next page exist.

Code for Pagination Results

//This is your query again, the same one... the only difference is we add $max into it

 $data_p = mysql_query("SELECT * FROM topsites $max") or die(mysql_error()); 

 //This is where you display your query results

 while($info = mysql_fetch_array( $data_p )) 

 { 

 Print $info['Name']; 

 echo "<br>";

 } 

 echo "<p>";

  // This shows the user what page they are on, and the total number of pages

 echo " --Page $pagenum of $last-- <p>";

 // 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) 

 {

 } 

 else 

 {

 echo " <a href='{$_SERVER['PHP_SELF']}?pagenum=1'> <<-First</a> ";

 echo " ";

 $previous = $pagenum-1;

 echo " <a href='{$_SERVER['PHP_SELF']}?pagenum=$previous'> <-Previous</a> ";

 } 

 //just a spacer

 echo " ---- ";

 //This does the same as above, only checking if we are on the last page, and then generating the Next and Last links

 if ($pagenum == $last) 

 {

 } 

 else {

 $next = $pagenum+1;

 echo " <a href='{$_SERVER['PHP_SELF']}?pagenum=$next'>Next -></a> ";

 echo " ";

 echo " <a href='{$_SERVER['PHP_SELF']}?pagenum=$last'>Last ->></a> ";

 } 

 ?>