Tech-Evangelist

Technical Articles, Musings and Opinions from Tech-Evangelist

  • Home
  • About
  • Guidelines
Previous article: The Relationship Between HDTV Refresh Rate and Frame Rate
Next article: Norton Internet Security May Be Disabling E-Mail

Using the MySQL LIMIT Clause for Paging with PHP

October 16, 2008 By Doogie - Copyright - All Rights Reserved

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.

Filed Under: MySQL Tutorials, PHP Tutorials, Web Site Development

Comments

  1. Sheba says

    December 16, 2008 at 10:52 pm

    Thanks you so much to solve my problem

  2. costel c says

    February 26, 2009 at 4:26 am

    very nice and functional script;thank you for sharing;you have
    Please remove tha last “>” from “echo “Next >>>”;:D

  3. Doogie says

    February 26, 2009 at 7:12 am

    Hi Costel

    Oops. Kudos, oh eagle-eyed one! You caught us with a typo.

  4. Geri says

    April 3, 2009 at 2:52 pm

    Thank you guys!!! You helped me so much, God bless you!!! It’s so difficult to see such a simple and perfect code on the web! And the explanations are great for a newbie like me! I hope one day I’ll be clever enough to explain to newbies 😉

  5. Liam says

    December 8, 2009 at 3:42 am

    I got this work work, thanks heaps!
    How can I hide the ‘1’ if the maximum outweighs the actual results? Like, if I want to display 15 results but there’s only 10 results that match the query, I don’t want ‘1’ appearing on the page.

    Thanks!
    Liam

  6. Doogie says

    January 3, 2010 at 7:00 pm

    Hi Liam

    The code has been modified with the if($total_results > $max_results) conditional statement. That should eliminate the ‘1’.

  7. Michael Kagan says

    March 22, 2010 at 6:59 am

    Thank you so very much, works a charm, saved me hours and hours of time and stress

  8. gila says

    January 26, 2011 at 4:08 am

    Grazie ragazzi, avete realizzato un bellissimo codice. Voleco chiedervi questo:
    Se nella SELECT si inserisce WHERE product_name=$product_name non si vedono le pagine oltre alla prima.
    Grazie!
    Gila

  9. PM Adhikari says

    August 1, 2011 at 5:57 am

    Thank you very much. I was also looking for the same thing.

  10. vjeko says

    March 30, 2012 at 5:42 am

    Thank you so much. I ‘ve wrote one big php/mysql intranet(web*)app for my company and this thread was something that i needed!
    Thanks one mor time… Aloha from Switzerland

    [*databease with 11’000’000 rows]

  11. Steve says

    July 28, 2012 at 7:51 am

    Hi, thanks for the tutorial, but when I use your code I end up with over 3,800 navigation page links (the $total_pages is BIG). How can I display only 15 links? Thx

  12. Doogie says

    July 29, 2012 at 6:53 am

    Hi Steve

    You either missed something in the code or you have a very large data set. This code never displays the total number of rows, but does display the total number of pages and a link to each. The LIMIT clause will only display the number of rows that you designate in $max_results on each page off results. It does not limit the number of links to individual pages of results. $total_pages is used to determine the number of links to pages of results.

    If your data set is extremely large, you could limit the number of links to display by only displaying the links to the first page number, the last page number and a few pages surrounding the current page number like this.

    1 . . . 6 7 8 9 10 11 12 13 14 15 16 . . . 31

    If that is what you are looking for, the code is different and more complex. It would require a separate tutorial.

Categories

  • Affiliate Marketing
  • CSS Tutorials
  • FileZilla Tutorials
  • Home Theater
  • Internet Marketing
  • Internet Technology
  • Kindle Tips
  • MySQL Tutorials
  • Online Auction Tips
  • Paint Shop Pro Tutorials
  • PHP Tutorials
  • Tech News
  • Thunderbird Tutorials
  • Video Production
  • Web Site Development
  • WordPress Tutorials
follow me on Twitter
Content and images are copyrighted by Tech-Evangelist.com and others

© 2021 Tech-Evangelist.com - All Rights Reserved
Posted code samples are free to use. Do not reproduce or republish articles or content on another web site.

Privacy Policy : Terms of Use