PHP Pagination With Drop Down Menu

How useful was this for/to you?

  • Very! It was helpful and easy to use.
  • Very useful but complicated to use.
  • Somewhat useful but easy to use.
  • Somehwat useful but complicated to us.
  • Not very helpful.
See results without voting

This is my very first hub so please hang in there with me while I get my footing.

This hub will show you how to implement pagination in a PHP script of your own. This is a second generation snippet. The original pagination code was written by the kind folks at Stranger Studios but for my personal use I really wanted to extend it further.

The original code would allow you (the programmer) to specify how many records you wanted to show per page. But when I use other sites (such as search engines or performing searches on other sites) I often want more records to be shown per page than the default. So I added a drop down menu of the possible numbers of records that can be shown on each page.

This code is free-for-use but I would appreciate it if the comments remained intact. You may use it for your own scripts and/or scripts that you may offer to other people for free or commercial use. If you use it and find it useful I would very much appreciate a link to this tutorial wherever it is offered. And please feel free to tell anyone else you know that may benefit from it's use.

Here are some things to bear in mind:

  1. At least a decent amount of PHP and MySQL knowledge will be necessary to use this for your own purposes.
  2. Within the HTML in the PHP code you can control the options in the select box (drop down menu). You can have more or less than are in the code and can change each option's amount.
  3. I like to offer a fairly high option but I keep the default lower to decrease the number of records returned by default to try to save on resources. Use a reasonable lower amount as the default.
  4. There are two pieces to the code, PHP and CSS. You can implement the css through a style tag in the head section of the HTML or you can save it in an external css file as I do and link the file in the head section.

Now the code and then the explanations:


<?php

// How many adjacent pages should be shown on each side?
$adjacents = 3;

/*
First get total number of rows in data table.
If you have a WHERE clause in your query, make sure you mirror it here.
*/
$query = "SELECT COUNT(*) as num FROM pages";
$total_pages = mysql_fetch_array(mysql_query($query));
$total_pages = $total_pages[num];

/* Setup vars for query. */
$targetpage = "all_pages.php"; //your file name (the name of this file)

/* This section by Christopher-Michael Snyder to ceate dropdown number-per-page selection */
$number_per_page = $_REQUEST['npp'];
$page = $_REQUEST['page'];

$requested_records = $number_per_page * $page - $number_per_page + 1;
 if ($requested_records > $total_pages) {
	$page = '1';
 }

if (isset($number_per_page)) {
$limit = $number_per_page;
}else{
$limit = 10; // How many items to show per page by default if visitor hasn't used dropdown.
}

echo "
<form action=\"$targetpage\" method=POST>
<input type=hidden name=page value=\"$page\">
Items Per Page: <select name=\"npp\">
<option selected>$limit</option>
<option value=10>10</option>
<option value=25>25</option>
<option value=50>50</option>
</select>
<input type=submit value=\"Go\">
</form>
";

if($page)
$start = ($page - 1) * $limit; //first item to display on this page
else
$start = 0; //if no page var is given, set start to 0

/* Get data. */
$sql = "SELECT * FROM pages LIMIT $start, $limit";
$result = mysql_query($sql);

/* Setup page vars for display. */
if ($page == 0) $page = 1; //if no page var is given, default to 1.
$prev = $page - 1; //previous page is page - 1
$next = $page + 1; //next page is page + 1
$lastpage = ceil($total_pages/$limit); //lastpage is = total pages / items per page, rounded up.
$lpm1 = $lastpage - 1; //last page minus 1

/*
Now we apply our rules and draw the pagination object.
We're actually saving the code to a variable in case we want to draw it more than once.
*/
$pagination = "";
if($lastpage > 1)
{
$pagination .= "<div class=\"pagination\">";
//previous button
if ($page > 1)
$pagination.= "<a href=\"$targetpage?page=$prev&npp=$limit\">ÃÃë previous</a>";
else
$pagination.= "<span class=\"disabled\">ÃÃë previous</span>";

//pages
if ($lastpage < 7 + ($adjacents * 2)) //not enough pages to bother breaking it up
{
for ($counter = 1; $counter <= $lastpage; $counter++)
{
if ($counter == $page)
$pagination.= "<span class=\"current\">$counter</span>";
else
$pagination.= "<a href=\"$targetpage?page=$counter&npp=$limit\">$counter</a>";
}
}
elseif($lastpage > 5 + ($adjacents * 2)) //enough pages to hide some
{
//close to beginning; only hide later pages
if($page < 1 + ($adjacents * 2))
{
for ($counter = 1; $counter < 4 + ($adjacents * 2); $counter++)
{
if ($counter == $page)
$pagination.= "<span class=\"current\">$counter</span>";
else
$pagination.= "<a href=\"$targetpage?page=$counter&npp=$limit\">$counter</a>";
}
$pagination.= "...";
$pagination.= "<a href=\"$targetpage?page=$lpm1&npp=$limit\">$lpm1</a>";
$pagination.= "<a href=\"$targetpage?page=$lastpage&npp=$limit\">$lastpage</a>";
}
//in middle; hide some front and some back
elseif($lastpage - ($adjacents * 2) > $page && $page > ($adjacents * 2))
{
$pagination.= "<a href=\"$targetpage?page=1&npp=$limit\">1</a>";
$pagination.= "<a href=\"$targetpage?page=2&npp=$limit\">2</a>";
$pagination.= "...";
for ($counter = $page - $adjacents; $counter <= $page + $adjacents; $counter++)
{
if ($counter == $page)
$pagination.= "<span class=\"current\">$counter</span>";
else
$pagination.= "<a href=\"$targetpage?page=$counter&npp=$limit\">$counter</a>";
}
$pagination.= "...";
$pagination.= "<a href=\"$targetpage?page=$lpm1&npp=$limit\">$lpm1</a>";
$pagination.= "<a href=\"$targetpage?page=$lastpage&npp=$limit\">$lastpage</a>";
}
//close to end; only hide early pages
else
{
$pagination.= "<a href=\"$targetpage?page=1&npp=$limit\">1</a>";
$pagination.= "<a href=\"$targetpage?page=2&npp=$limit\">2</a>";
$pagination.= "...";
for ($counter = $lastpage - (2 + ($adjacents * 2)); $counter <= $lastpage; $counter++)
{
if ($counter == $page)
$pagination.= "<span class=\"current\">$counter</span>";
else
$pagination.= "<a href=\"$targetpage?page=$counter&npp=$limit\">$counter</a>";
}
}
}

//next button
if ($page < $counter - 1)
$pagination.= "<a href=\"$targetpage?page=$next&npp=$limit\">next ÃÃû</a>";
else
$pagination.= "<span class=\"disabled\">next ÃÃû</span>";
$pagination.= "</div>\n";
}


echo "<table>"; // this is to put all of the records into a table. This is up to you.

while($row = mysql_fetch_array($result)) {

/*
This is where you will put the code to display the records that you want shown.
It's also where you can format the php/html code to have it display as you need.
*/

}


echo "</table>"; // ending the table.
echo "<br /><br />";
echo "$pagination";

?>

Explanations:

   1. The value of $adjacents tells the code how many links should be shown on either side of the current page number in the pagination. This may change depending on what you need the width to be in order to not mess with your design. I would go with 2 or 3 adjacents if you have the room. Feel free to go more with room if your design will allow it.
   2. $targetpage should be the name of the page this script will be included in. The form for the drop down menu returns to this same page as do the pagination links. An incorrect value here will allow the page to allow correct initially but will lead to Page Not Found or similar server errors when someone tries to change the number of records per page through the form or clicks on a pagination link.
   3. $limit is where you set the default number of records to be shown on each page until a user changes the amount by changing the value in the drop down box and submits the form. Please see my list above regarding what to keep in mind when establishing your default amount. Generally it's good practice to set the limit to an amount you offer in the drop down menu.
   4. Be sure to echo $pagination as the code has stored everything within the variable.
   5. You can change the order of where you have the while loop that displays the records to have the the pagination, drop down item number form and records in different orders. You could also sandwich the records between two paginations and forms so that the records will have drop down menus and/or pagination at the top and bottom of the page. This could be a good idea for pages with MANY records so that the user doesn't have to scroll up and/or down a ton of records to get to the form or pagination again.

Lastly it's time for the css code. It's pretty self explanatory but feel free to mess around with the classes to see what each one does. It's quite customizable.

div.pagination {
padding: 3px;
margin: 3px;
}

div.pagination a {
padding: 2px 5px 2px 5px;
margin: 2px;
border: 1px solid #AAAADD;
text-decoration: none; /* no underline */
color: #000099;
}
div.pagination a:hover, div.pagination a:active {
border: 1px solid #000099;
color: #000;
}
div.pagination span.current {
padding: 2px 5px 2px 5px;
margin: 2px;
border: 1px solid #000099;
font-weight: bold;
background-color: #000099;
color: #FFF;
}
div.pagination span.disabled {
padding: 2px 5px 2px 5px;
margin: 2px;
border: 1px solid #EEE;
color: #DDD;
}

Tips on Drop Menu Options

One nice feature of the code above is the flexibility for you to offer as many or as few number options in the drop down menu as you want and exactly what they are.  But some may not be sure how many or what number options to offer.  So here's a list of things to consider:

  1. You generally do not need a whole lot of options.  Many will be perfectly fine with three four options in the drop down menu. If you have under a couple hundred records to show you can go with 2 or 3 because more than that and there'd be only a 5-10 records difference per page and the difference is negligible.
  2. If you have thousands of records to show then you should make sure that your maximum option isn't too low.  25 records per-page is a fine maximum with only a couple hundred but could became a nuisance when dealing with A LOT of records.
  3. One thing to consider when setting your maximum per-page option can be your layout.  If your layout is very dependent on the size of the &lt;div&gt; or whatever page element contains your records then having too many records shown on each page could blow out your layout.  Estimate how many records you think you can show per page to fit within the confines of your site's layout.  Then test it by going to the page and selecting the maximum number from the drop down and see if your layout remains the same.
  4. If your layout is regrettably too dependent and doesn't let you show a whole as many records as you'd like to offer as a maximum you could the tutorials code inside a scrolling DIV (here's a tutorial on that if needed!). You can set the dimensions of the DIV to fit your layout and then if the records printed are too many the browser will make them scroll within the div and not break the layout.
  5. And remember that you can ALWAYS change the available options in the drop down menu at any time should your total number of records grow..which would probably be a good thing!  While you have less records to show you can keep the maximum lower and over time when you have far more records you can add more options to the html for the drop down menu and change the available options.

Comments 9 comments

weekendrockstar profile image

weekendrockstar 5 years ago from SE Pennsylvania Author

After testing this in my own application I've realized that I need to correct a problem. It was minor but it was bugging me. I've added the code to my example above for anyone that uses it from now on. I am going to show the code to be added here so anyone that used the first version already can easily copy/paste it into their code.

The problem: If a user were on the fifth page of results but then used the drop down box to increase the number of records shown per page and there weren't enough records then no records would be shown.

If you have 50 records to be displayed and someone were viewing with 10 records per page and were on page 3 (showing records 31-40) but then they used the drop down box to change it to 25 records per page then page 3 would try to show records 51-75. But your records stop at 50.

The first version would just show a blank screen with no records because none exist in that range. The change will multiply the page number times the number of records to be shown per page and if the first number is higher than the number of records shown it will now revert to page one and then all records should be shown.

The code:

$requested_records = $number_per_page * $page;

if ($requested_records > $total_pages) {

$page = '1';

}

Look at the PHP code and you can see it at lines 65-67. Paste it there in your code and it'll handle the rest.


Gorgar 5 years ago

Hey WERockStar. Thx for posting your pagination code. Hard to find any really good examples.

Just set it up and noticed that the last page of records does not get displayed. My db has 377 records and no matter what number of records I choose to display, the last page, ( which always has less than a full page of records) defaults back to page 1.

I have limited PHP experience and have been unable to determine why this is occurring. Ideas?


weekendrockstar profile image

weekendrockstar 5 years ago from SE Pennsylvania Author

@Gorgar: Thank you for your comment and you're welcome for posting it.

Unfortunately when I posted this it was still in a testing phase apparently. I actually have fixed it (I believe)..as it now works as it should. I will be making the changes in the hub's code and will comment when the change is made. You will just have to copy the new algorithm on the line that I will specify and replace the code that you now have on that line.

The problem is caused by the algorithm that I used to detect if the user were changing the per_page number on a page greater than 1 where the MySQL query was looking for records that didn't exist. Such as if you had it default to 10 records per page with 50 records in the database and they navigated to the 3rd page (records 21-30) and then changed the drop down value to 50 records per page then the MySQL query would would be looking for records 101-150 which do not exist in the database.

So the algorithm is supposed to detect if the number of records shown on any page greater than one when the form is submitted to show records that do not exist it reverts it back to page one. The algorithm at the time that you used my code was multiplying the page number requested by the records shown per page. The problem with that was if you have 10 records per page and request page 5 it then be checking to see that there are 50 records total that are to be shown. But if you have 48 records this would return as FALSE and the link for page 5 would send you to page 1 even though there should be a 5th page to show records 41-50.

Thanks for letting me know that someone wanted the update. I fixed this in my own program but forgot to update my tutorial's code. The fix should be reflected on here within an hour or two!


weekendrockstar profile image

weekendrockstar 5 years ago from SE Pennsylvania Author

The fix has been applied! Copy line 21 of the pagination code and replace the existing line in your code with it. If you've used a non-fixed version the line you are replacing will be $requested_records = $number_per_page*$page.

I am also going to add a new text container towards the end of the tutorial with a couple tips on what to use as possible number values in your drop down menu.

Please feel free to 'follow' me here on hubpages for any updates on this code or to be notified when I post new hubs. I spent 3 hours EARLY this morning writing a whole new php tutorial which is working I just have to write the actual hub. This will help keep you in-the-loop! =0)


Gorgar 5 years ago

Hey WERS,

Great job on the fix!! Put it through all the nums per page and all are working correctly. I've got a sorting script where that I'm going to try to implement in to it and see how that works. This is best little pagination script that I've come across, and I've tested quite a few. Great job and keep up the good work! Cheers!


weekendrockstar profile image

weekendrockstar 4 years ago from SE Pennsylvania Author

Sorry for not responding sooner...sooo busy but I did immediately approve your comment anyway =0) It does it's job and I believe quite well. I am not sure why 50% of the people who took the poll on this page said it's "Not very helpful". If you want pagination, this is useful. If you want pagination with a dropdown menu to allow each user to select how many results they want per page (seems like a lot of people would as MOST sites that allow you to search for info/items on their site have this option) then this is doubly helpful.

But I definitely appreciated your comment as it gives me all the more reason to post such things!


MDN 4 years ago

Hey Weekendrockstar. I signed up just to say a huge thanks for this. This is exactly exactly what I was looking for. All other pagination tutorials/guides/examples I've come across have been fairly rubbish and no where near as well documented as yours is. I've implemented it in a little project of mine and it works flawlessly.

Big thanks again.


weekendrockstar profile image

weekendrockstar 3 years ago from SE Pennsylvania Author

You're very welcome! And I appreciate the feedback as it lets me know that at least another person out there is benefits not only from my initial work in creating it but then also the work to create a hub and make it as informative as I can without being overly 'heavy'.


Johna55 2 years ago

I have not checked in here for a while since I thought it was getting boring, but the last few posts are good quality so I guess I'll add you back to my daily bloglist. You deserve it my friend dbkdgegcdegb

    Sign in or sign up and post using a HubPages Network account.

    0 of 8192 characters used
    Post Comment

    No HTML is allowed in comments, but URLs will be hyperlinked. Comments are not for promoting your articles or other sites.


    Click to Rate This Article
    working