Jun
26
2008

PHP howto – Database paging (pagination)

OK, so it’s not quite the nuclear bomb I promised, but it’s just as much fun :)

Database paging, for those of you who are interested, is when you split the number of results returned by a query into smaller chunks, and then show those one page at a time.  Think of how Google will display 10 results out of 4 236 735.  Same thing.

The basic idea is to:

  1. Run your query, limited to the number of desired results
  2. Get the number of results that there would have been, without the limit
  3. Display the first set of results
  4. Build and display <prev> and <next> links, which, when clicked…
  5. Display the prev/next set of results, moved down/up by the desired amount
  6. Repeat 4 & 5

The following code sample is a very basic implementation of this idea.  I have not checked the code, so apologies in advance if there are any bugs.



". $row['id'] ."


". $row['name'] ."


". $row['surname'] ."
"; } // Don't bother building paging if we don't have records if ($no_results) { $line_output = "No records found..."; $page_output = ""; } else { // Build and links and save to $page_output $rs_prev = $row_start - $howmany; // where would prev page start, given current start less no. of records $rs_next = $row_start + $howmany; // where would next page start, given current start plus no. of records // If for some reason the next starting point is negative, do not display // This happens when our current starting point is already 0 // This may happen if some smartass manually changes the rs= bit in the url $page_output_prev = ($rs_prev < 0) ? "" : "Previous"; // Will the next page jump start point exceed the number of records returned? // If so, don't display ' $page_output_next = ($rs_next >= $count_result) ? "" : "Next"; // Just something to put between & , IF they are both active if (($page_output_prev == "") || ($page_output_next == "")) {$page_output_breaker = "";} else { $page_output_breaker = " || ";} // Build final paging output $page_output = $page_output_prev . $page_output_breaker . $page_output_next; } // Write the outputs echo $line_output; echo $page_output; ?>

A few points worth taking note of:

Row counting

To get the total number of results, I have used

SELECT SQL_CALC_FOUND_ROWS

followed by a second query

SELECT FOUND_ROWS() AS total

As stated in the comments, this will return the number of results that there would have been without a limit clause.  There are other ways to achieve this, namely using count() in a second query, but this way is apparently quicker, and also slightly cleaner code.

Building the paging links

In the code I have used

$_GET['rs']

What this does is get the value from the part of the URL that looks something like http://www.yoursite.com/index.php?rs=10

That value then becomes our next starting point, and is injected into the SQL query.

I’ve seen some tutorials where page numbers are used instead of starting records.  This is fairly easy to achieve, and involves dividing the number of records returned by the size of the desired result set to get the number of pages, and then multiply again when determining the next starting point for the limit.  I’ve not done that in this tutorial for the sake of simplicity.  Besides, Google uses the records, and not pages, method.  Can’t be that terrible :)

Extending the functionality

In this example I’m echoing the result to screen.  You could instead wrap this up in a function and return the results.  Another easy modification would be to alternate the background colours, as shown in my previous howto.

And that’s it for today.  If you found this useful, of would like to improve it, comments are always appreciated!

Tags: , , , , , , ,

This entry was posted on Thursday, June 26th, 2008 at 11:06 am and is filed under php. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

«
»

18 Responses to “PHP howto – Database paging (pagination)”

  1. Janice says:
    July 9, 2008 at 9:25 pm

    Thanks for this script; I’ve been struggling with pagination for several days, now, and this really helped me.

    However, there is a bug: please change
    “echo $paging_output;” to “echo $page_output;”, otherwise your pages don’t show up.

    Thanks again,
    -Janice

    Reply
  2. Denham Coote says:
    July 9, 2008 at 9:29 pm

    Aah, yes. Well spotted. I though something would crop up. All fixed. Glad I could help.

    Thanks,

    Denham.

    Reply
  3. Alex Malkin says:
    August 1, 2008 at 4:20 pm

    I am having a bit of trouble with this. I made a database with all the right rom names ect but the page numbers and link to the next pages dont show up.
    The page is http://jbvideos.malkinmedias.com/test.php
    What am i doing wrong. Ps there are 2 rows in the table with data but i changed it to display 1 per page to test.

    Thanks anyway
    Alex Malkin

    Reply
  4. Brian says:
    December 29, 2008 at 5:54 am

    Thanks man that really helped me

    Reply
  5. Manali says:
    January 22, 2009 at 1:02 pm

    It’s excellent…very nice explanation is provided by you in a simpler manner!!!

    Thanks,
    Manali Shah

    Reply
  6. Syed Zafar Ali Shah says:
    February 13, 2009 at 9:24 am

    Thanks a lot
    this concept solve my problem
    thanks

    Syed Zafar Ali Shah
    Computer Software Engineer

    Reply
  7. wakhuta says:
    March 6, 2009 at 1:49 pm

    just taken a snap at your code. i hope this gonna help me ease pagination problem. if it wont work, i will then consult you again.
    thamks

    Reply
  8. Chris says:
    March 19, 2009 at 5:11 pm

    Works Perfectly! Thank you very much. This is exactly what I needed.

    Reply
  9. Denham Coote says:
    March 19, 2009 at 5:12 pm

    Glad to have helped a few people :)

    Reply
  10. Pradeep says:
    March 24, 2009 at 2:22 pm

    It helped me a lot……thank you So much…..Have a good day ahead……………..

    Reply
  11. Quentin - Womens Clothes says:
    August 27, 2009 at 11:45 am

    Nice one! Just type for this into google and out popped this little gem. Perfect for what I was after so a big thank you for sharing.

    Reply
  12. jim says:
    January 15, 2010 at 10:51 pm

    Great Help. Is it possible to put the output into a table format?

    Reply
  13. jim says:
    January 16, 2010 at 12:27 am

    Thanks. I figured it out.

    Reply
  14. Simon - Voucher Codes says:
    January 27, 2010 at 4:30 pm

    Nice one, works great and I’ve managed to change it to a style I like for my sites.

    Reply
  15. Ramesh says:
    February 17, 2010 at 9:44 am

    plese send to send complete display data base list with paging

    Reply
  16. Matthew R. Ferry says:
    March 13, 2010 at 8:24 pm

    VERY VERY NICE. VERY SIMPLE.
    Works great!!! Nice work

    Reply
  17. Retail Therapy says:
    September 5, 2010 at 8:22 am

    hi there! diggin’ the website. I attempted signing up for your RSS and couldn’t. Just informing you! Take care! :)

    Reply
  18. lena says:
    December 7, 2010 at 3:55 pm

    Hi

    can you please help me with this script I cant realyy get the point, i really need to do pagination on my project and think that thisis what i’m looking for, but it is not so clear for me.
    Please help!!! :(

    Reply

Leave a Reply