Using the MySQL LIMIT Clause for Paging with PHP

One of the first questions that comes up when someone starts using PHP and the very powerful MySQL database is: How do I page through the results of a query like I see with search results on many web sites? This is pretty easy to accomplish if you know how to use the MySQL LIMIT clause.

The LIMIT clause allows you to select a numeric range of results from a recordset that would normally be returned from a SELECT query. But rather than returning the entire set of results from the query, the LIMIT clause literally limits the results to a sequential group that you pre-determine.

There are several ways to accomplish paging using PHP. The script method we will use here is fairly easy to understand and is very reliable. We will assume that you have already set up your database connection in your code, so we will get right into the method for creating pages. We this example we are going to display pages of results from an inventory table, but the script can easily be modified for search results or anything you wish to use it for.

There are three parts to the code that we will use.

The following PHP code needs to be placed at the top of the script.

<?php
if (isset($_GET['page'])) $page = $_GET['page']; else $page = 1;   
$max_results = 10; 
$from = (($page * $max_results) - $max_results); 
?>

The first line checks to see if a variable named $page has been passed using a GET method via a querystring attached to the page’s URL. We will be using name-value pairs in querystrings to pass the parameters that the script needs to determine which page of results to display. If no value has been set for $page, the default is set to 1.

The second line sets the maximum number of results you wish to see displayed. You can set this to any number that you want to display.

The $from variable calculates the starting point in the set of results that would normally be returned as a result of a SELECT query. All the rows that are returned from a query are numbered sequentially, starting with zero. If you are viewing the first page of results, $page = 1 and $max_results = 10, so ((1 * 10) – 10) = 0, which is the starting point. If you want to view the page 2 results, ((2 * 10) – 10) = 10, which is the starting point for the next group of 10 rows.

Next, place the SELECT query in your wherever you want the results to display. in this example, we are going to display product names and their product IDs. We use the LIMIT clause to return only the range that we have pre-determined. The LIMIT clause is added to SELECT query statement using the syntax: LIMIT start-of-range, number-of-results.

<?php
$sq = "SELECT product_name, product_id FROM inventory ".
      "ORDER BY product_name LIMIT $from, $max_results";

$rs = mysql_query($sql);

while($row = mysql_fetch_array($rs))
{ 
    echo $row['product_name'] . " " . $row['product_id'] . "<br />";
}
?>

The controls can be placed above or below the query results being displayed. The sample code used below will display a list of page numbers. If you click on any page number, it will refresh the page and display the results for that page. I have also included links for the Previous and Next page. This part is a little more complex.

<?php
// (1) get the total number of results for your query
// modify this to match the total results for the main query
$total_results = mysql_result(mysql_query("SELECT COUNT(*) as Num 
FROM inventory"),0); 

// (2) Calculate total number of pages. Round up using ceil() 
$total_pages = ceil($total_results / $max_results); 

if($total_results > $max_results)
{
  // (3) build Previous link
  if($page > 1)
  { 
     $prev = ($page - 1); 
     echo "<a href=\"".$_SERVER['PHP_SELF']."?page=$prev\">&lt;&lt; Previous</a> "; 
  } 

  // (4) display page numbers
  for($i = 1; $i <= $total_pages; $i++)
  { 
     if($page == $i)
     { 
        echo $i . " "; 
     } 
     else 
     { 
        echo "<a href=\"".$_SERVER['PHP_SELF']."?page=$i\">$i</a> "; 
     } 
  } 

  // (5) build Next Link 
  if($page < $total_pages)
  { 
       $next = ($page + 1); 
       echo "<a href=\"".$_SERVER['PHP_SELF']."?page=$next\">Next &gt;&gt;</a>"; 
  } 
}
?>

Let’s see is we can explain this in sections.

$total_results is the number of rows that would be returned if you did not use the MySQL LIMIT clause. If you are filtering the results with a WHERE clause, your need to filter this query, as well. You can eliminate both the ORDER BY and LIMIT clauses.

For example, if the query used to display results is

$sq = "SELECT product_name, product_id FROM inventory WHERE category_id=6 ".
      "ORDER BY product_name LIMIT $from, $max_results";

The $total_results query would looks like this:

$total_results = mysql_result(mysql_query("SELECT COUNT(*) as Num 
FROM inventory WHERE category_id=6"),0); 

The second section of code calculated the total number of pages.

The third section sets up a Previous link. If you are viewing page one, there isn’t any Previous page, so it is not displayed.

The fourth section displays the page links. The hyperlinks created simply link to the current page ($_SERVER[‘PHP_SELF’) and add a query string with the name-value pairs that will be used to assign a value to the $page variable at the top of the script (?page=2).

The fifth section follows up with a Next link, but doesn’t display the link if you are viewing the last page.

For someone who is just starting to work with the MySQL database, this may seem complex, but it really isn’t. Just take it line by line and study it to determine how the code works.