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\"><< 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 >></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.
Thanks you so much to solve my problem
very nice and functional script;thank you for sharing;you have
Please remove tha last “>” from “echo “Next >>>”;:D
Hi Costel
Oops. Kudos, oh eagle-eyed one! You caught us with a typo.
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 😉
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
Hi Liam
The code has been modified with the if($total_results > $max_results) conditional statement. That should eliminate the ‘1’.
Thank you so very much, works a charm, saved me hours and hours of time and stress
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
Thank you very much. I was also looking for the same thing.
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]
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
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.