Quantcast

PHP Pagination with MySQL

Get the WebProNews Newsletter:


[ Business]

It is extremely common these days to make results display across multiple pages. Some examples are maybe browsing through picture galleries, store products, blog entries, etc.

Unless you are a veteran programmer, this might seem a little intimidating at first. Rest assured, it is an easy task.

In order to create your own paging system, you will need to get some information first. You will need the total number of list entries, number of results per page, current page number being viewed, the total number of pages and the offset.

Lets start with the total number of list entries. In order to do this, we need some test data in a MySQL table. For demonstration purposes, we are going to use the following MySQL table:

CREATE TABLE products ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY NOT NULL, name VARCHAR(50) NOT NULL, description TEXT NOT NULL ); INSERT INTO products (name,description) VALUES ('Product 1','Description for Product 1'); INSERT INTO products (name,description) VALUES ('Product 2','Description for Product 2'); INSERT INTO products (name,description) VALUES ('Product 3','Description for Product 3'); INSERT INTO products (name,description) VALUES ('Product 4','Description for Product 4'); INSERT INTO products (name,description) VALUES ('Product 5','Description for Product 5'); INSERT INTO products (name,description) VALUES ('Product 6','Description for Product 6'); INSERT INTO products (name,description) VALUES ('Product 7','Description for Product 7'); INSERT INTO products (name,description) VALUES ('Product 8','Description for Product 8'); INSERT INTO products (name,description) VALUES ('Product 9','Description for Product 9'); INSERT INTO products (name,description) VALUES ('Product 10','Description for Product 10');

Now that we have a MySQL table full of “fake” products, lets get the paging working!

First, we need the total number of list entries. To do this, we will use:

MySQL's COUNT() function. $result = mysql_query("SELECT COUNT(*) AS total_entries FROM products") or die(mysql_error()); $row = mysql_fetch_row($result); $total_entries = $row[0];

Now we have the total entries retrieved and stored in the $total_entries variable.

Next, we need to get the number of results per page. It is a common programming practice when developing scripts to use a central configuration file (such as config.php). For the sake of simplicity, we’ll call our variable $entries_per_page.

$entries_per_page = 3;

Next, we need to get the current page number being viewed. We will call our page number variable >$page_number.

if(isset($_GET['page_number'])) { $page_number = $_GET['page_number']; } else { $page_number = 1; }

This means set $page_number to the page_number specified via query string if it exists, otherwise set it to page number 1.

Next, we need to get the total number of pages that there will be. An important thing to remember is that even if the very last page contains only one product, that one product will still be one page.

To do this, we use PHP’s ceil() function. This means round up the number to the nearest integer. For example, ceil(2.1) will round up to 3. ceil(2.9) will round up to 3. In order to figure out the total number of pages, you simply do ceil(TOTAL ENTRIES / ENTRIES PER PAGE).

$total_pages = ceil($total_entries / $entries_per_page);

For this specific example, $total_pages has the value 4.

Finally, we need to determine the offset. What exactly is the “offset”? The offset is the number of the first entry to pull. It is what you use to tell MySQL where to start fetching data. For example, if we are viewing products on page two, we would want to return only products 4 through 7. If we are viewing products on page three, we would want to return only products 8 through 10. To get an offset, you simply do:

(CURRENT PAGE - 1) * ENTRIES PER PAGE

$offset = ($page_number - 1) * $entries_per_page;

Now that we have all of the data we require we are able to use MySQL to pull all products within our “page” being viewed.

$result = mysql_query("SELECT * FROM products LIMIT $offset, $entries_per_page") or die(mysql_error()); while($obj = mysql_fetch_object($result)) { // Display the data however you want here. print <<id - $obj->name - $obj->description<br>

EOD; }

And lastly, the whole point of this article is to demonstrate page numbering so now we will display the page numbers! What we are going to do is iterate through a loop that loops through all page numbers and makes them a link unless it’s the current page.

for($i = 1; $i <= $total_pages; $i++) { if($i == $page_number) { // This is the current page. Don't make it a link. print "$i "; } else { // This is not the current page. Make it a link. print " <a href="products.php?page_number=$i">$i</a> "; } }

Of course, you could (and I encourage you to) experiment with the code shown here to make it display however you wish.

You can view a complete example of this script in action at http://www.phplabs.com/articles/products.php.

Add to Del.icio.us | DiggThis | Yahoo! My Web | Furl

Mike is an experienced web developer with a wide range of programming capabilities. He has been delivering top quality web applications for several years primarily using Perl/CGI and PHP combined with MySQL. His ability to deliver high quality solutions while meeting deadlines has put his services in high demand.

PHP Pagination with MySQL
About Michael Rogne
Mike is an experienced web developer with a wide range of programming capabilities. He has been delivering top quality web applications for several years primarily using Perl/CGI and PHP combined with MySQL. His ability to deliver high quality solutions while meeting deadlines has put his services in high demand. WebProNews Writer
Top Rated White Papers and Resources
  • http://seanhagen.ca Sean Hagen

    What about when there are rows missing? For example, a user may delete posts from a blog, leaving rows missing. Then you may have id’s jumping one or more at a time ( ie, 1,2,4,5,6,8,10 ). How would you deal with this? I’ve been working on this for a few days now, and it’s starting to bug me.

    So far the only idea I’ve is to try and figure out if there are any missing ID’s on the last statement ( ie, your last page showed items 6,8, and 10 ), and then move forward the proper ammount. I’m still working on it, and hopefully I’ll have something soon.

  • http://seanhagen.ca Sean Hagen

    It figures that right after posting that comment, I figure it out.

    Using ‘ LIMIT x OFFSET y ‘ in your sql query takes care of all of that for you. In your example above, x would be $offset, and y would be ( $entries_per_page * $page_number ). I tried using the ' LIMIT x,y ' form on my version of MySQL, but it doesn't like me very much ( or more likely I'm just doing it wrong ).

  • http://www.infinitywebcreations.com/ Mike Rogne

    Thanks for responding with your fix, Sean!

  • http://www.chatxplanet.com Dan

    Hello and thank you for an awesome pagination tutorial!

    I do have a problem with getting it to work properly when it comes to actually clicking the link that is formed during pagination. Every time I click on one of the pagination numbers (EG. the number 2) I get a 404 page not found error. I’ve tried a few things but cant seem to fix this. Anything special needing to be done?

    Thanks!

  • Tatianan Guanangui

    Fantastic Page!! easy and powerful.

  • Guest

    thanx miky 

  • coolman

    thanks alot very easy and self explained, appreciate your help, keep up the good work

  • http://a9webdesign.com Al Toman

    Mike,

    Thank you for the time and effort that you put into this tutorial.  You targeted those such as I, less experienced in mysql then I would like to be.

    With a few fixes, it ran beautifully.  I’ll take your encouragement and modify it into the script that I am working.

    I’ve read through many a tutorial on this matter (rather simple once learned) and came out of it worse.

    Then I came upon your writing.  Super~!

    Thanks again.

    Kind regards,

    Al Toman

     

     

     

  • Chris

    Thanks so much for this easy to understand code. It slipped perfectly into a new site I’m building with very few tweaks at all.

     

  • http://www.softgains.com Dharm Raj

    Thanks It is very helpful in case of paging

  • Join for Access to Our Exclusive Web Tools
  • Sidebar Top
  • Sidebar Middle
  • Sign Up For The Free Newsletter
  • Sidebar Bottom