Easy PHP Pagination

17 December 2009| 21 Comments| Print

I’ve had a few pagination scripts over the years but I thought i’d share the one that i’m currently using as it’s a useful script to have in your toolbox. As a developer you’ll soon find a need to paginate data when displaying contents from the database, and rather than use JavaScript which could require all the data to be loaded into the page on load, we can use PHP to ensure that we’re only requesting the data that we need from the database. For those who have no clue what i’m talking about. Pagination is a way of splitting up data into manageable chunks to display to a user, if you’ve spent more than two minutes on the internet chances are you’ve come into contact with some form of pagination. as a simple example you can checkout my homepage, at the bottom you’ll see a list of page numbers allowing you to browse through pages of data, this script was originally written by the guys over at Stranger Studios and allows you to split data up with it being paginated across however many pages dynamically.

The Code

pag

To start with you’ll see that we have our database connection file which we’re including into the page. Next you’ll see we’re setting three variables. ‘$tableName’, this refers to the table in your database that you want to get the data from, in our case it’s ‘countries_list’, ‘$targetpage’ refers to whatever you’ve saved the file as, in the case of the demo we’re saving the file as ‘index.php’. ‘$limit’ is the number of rows per page that we want to display from the database in the case of the demo you’ll see that we’re displaying just 10 rows.

Here are the main bullet points of what this file does in the simplest form.

  • Set the three variables at the top of the page declaring our database table, document filename, and number of results to display per page
  • Next we connect to the database and count the number of rows, so we know exactly how much data we’re dealing with
  • As the current page number is passed as a GET variable we check to see if a page number has been passed if there isn’t one then we set it to the first page
  • Then we have the query for the page content
  • Now we work out the next and previous page number as well as the last page number
  • Now we have a set of if statements, to determine which page the user is currently on relative to the total pages, so we can work out whether we need to display the first two page numbers with dots etc
  • Finally we echo ‘$paginate’ which will display our page numbers
  • Then we have our while loop which you can modify to suit your contents
<?php
	include('connect.php');	

	$tableName="countries_list";
	$targetpage = "index.php";
	$limit = 10; 

	$query = "SELECT COUNT(*) as num FROM $tableName";
	$total_pages = mysql_fetch_array(mysql_query($query));
	$total_pages = $total_pages[num];

	$stages = 3;
	$page = mysql_escape_string($_GET['page']);
	if($page){
		$start = ($page - 1) * $limit;
	}else{
		$start = 0;
		}	

    // Get page data
	$query1 = "SELECT * FROM $tableName LIMIT $start, $limit";
	$result = mysql_query($query1);

	// Initial page num setup
	if ($page == 0){$page = 1;}
	$prev = $page - 1;
	$next = $page + 1;
	$lastpage = ceil($total_pages/$limit);
	$LastPagem1 = $lastpage - 1;					

	$paginate = '';
	if($lastpage > 1)
	{	

		$paginate .= "<div class='paginate'>";
		// Previous
		if ($page > 1){
			$paginate.= "<a href='$targetpage?page=$prev'>previous</a>";
		}else{
			$paginate.= "<span class='disabled'>previous</span>";	}

		// Pages
		if ($lastpage < 7 + ($stages * 2))	// Not enough pages to breaking it up
		{
			for ($counter = 1; $counter <= $lastpage; $counter++)
			{
				if ($counter == $page){
					$paginate.= "<span class='current'>$counter</span>";
				}else{
					$paginate.= "<a href='$targetpage?page=$counter'>$counter</a>";}
			}
		}
		elseif($lastpage > 5 + ($stages * 2))	// Enough pages to hide a few?
		{
			// Beginning only hide later pages
			if($page < 1 + ($stages * 2))
			{
				for ($counter = 1; $counter < 4 + ($stages * 2); $counter++)
				{
					if ($counter == $page){
						$paginate.= "<span class='current'>$counter</span>";
					}else{
						$paginate.= "<a href='$targetpage?page=$counter'>$counter</a>";}
				}
				$paginate.= "...";
				$paginate.= "<a href='$targetpage?page=$LastPagem1'>$LastPagem1</a>";
				$paginate.= "<a href='$targetpage?page=$lastpage'>$lastpage</a>";
			}
			// Middle hide some front and some back
			elseif($lastpage - ($stages * 2) > $page && $page > ($stages * 2))
			{
				$paginate.= "<a href='$targetpage?page=1'>1</a>";
				$paginate.= "<a href='$targetpage?page=2'>2</a>";
				$paginate.= "...";
				for ($counter = $page - $stages; $counter <= $page + $stages; $counter++)
				{
					if ($counter == $page){
						$paginate.= "<span class='current'>$counter</span>";
					}else{
						$paginate.= "<a href='$targetpage?page=$counter'>$counter</a>";}
				}
				$paginate.= "...";
				$paginate.= "<a href='$targetpage?page=$LastPagem1'>$LastPagem1</a>";
				$paginate.= "<a href='$targetpage?page=$lastpage'>$lastpage</a>";
			}
			// End only hide early pages
			else
			{
				$paginate.= "<a href='$targetpage?page=1'>1</a>";
				$paginate.= "<a href='$targetpage?page=2'>2</a>";
				$paginate.= "...";
				for ($counter = $lastpage - (2 + ($stages * 2)); $counter <= $lastpage; $counter++)
				{
					if ($counter == $page){
						$paginate.= "<span class='current'>$counter</span>";
					}else{
						$paginate.= "<a href='$targetpage?page=$counter'>$counter</a>";}
				}
			}
		}

				// Next
		if ($page < $counter - 1){
			$paginate.= "<a href='$targetpage?page=$next'>next</a>";
		}else{
			$paginate.= "<span class='disabled'>next</span>";
			}

		$paginate.= "</div>";		

}
 echo $total_pages.' Results';
 // pagination
 echo $paginate;
?>

<ul>

<?php 

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

		echo '<li>'.$row['country'].'</li>';

		}

	?>
</ul>

A bit of style

Here’s the css to style you pagination

.paginate {
font-family:Arial, Helvetica, sans-serif;
	padding: 3px;
	margin: 3px;
}

.paginate a {
	padding:2px 5px 2px 5px;
	margin:2px;
	border:1px solid #999;
	text-decoration:none;
	color: #666;
}
.paginate a:hover, .paginate a:active {
	border: 1px solid #999;
	color: #000;
}
.paginate span.current {
    margin: 2px;
	padding: 2px 5px 2px 5px;
		border: 1px solid #999;

		font-weight: bold;
		background-color: #999;
		color: #FFF;
	}
	.paginate span.disabled {
		padding:2px 5px 2px 5px;
		margin:2px;
		border:1px solid #eee;
		color:#DDD;
	}

	li{
		padding:4px;
		margin-bottom:3px;
		background-color:#FCC;
		list-style:none;}

	ul{margin:6px;
	padding:0px;}

demodownload

21 Comments

  • Dan Cunningha

    Top man! Thanks for sharing.
    This is possibly one of the cleaner examples around on the internet to date.

    No doubt it will help a fair few.

    Cheers!
    Dan.

    17 December 2009 at 1:54 pm
  • uberVU - social comments

    Social comments and analytics for this post…

    This post was mentioned on Twitter by umutm: Easy PHP Pagination – http://bit.ly/7ifx3q #php…

    17 December 2009 at 9:18 pm
  • Norris Jay

    Nice. :) This is similar to http://www.phpeasystep.com/phptu/29.html

    18 December 2009 at 3:20 am
  • Ashley (author)

    @Norris it’s based on exactly the same script which was originally written by the guys over at Stranger Studios

    18 December 2009 at 5:49 am
  • Limno

    Excellent Post,

    Perfectly working…..Need this kinds of post to help us…

    Regards
    Limno.

    18 December 2009 at 7:30 am
  • raveren

    PHP4? Why?

    18 December 2009 at 2:13 pm
  • Ashley (author)

    @Raveren, I have no idea what your referring to, there is nowhere in my article that refers to PHP4.

    18 December 2009 at 3:23 pm
  • Ben

    Could you upload the data you used for the database? It would be really useful

    18 December 2009 at 4:45 pm
  • Monkeytail

    Nice! ( though I’am not a big fan of all the if-else nesting going on)
    Spice it up with a little bit more of code-commenting

    21 December 2009 at 12:46 am
  • Gijs

    You can better use SQL_CALC_FOUND_ROWS so you dont have to create two querys that are doing the same.

    21 December 2009 at 9:03 am
  • Davinder

    You can easily do this in ASP.NET MVC!

    22 December 2009 at 11:12 am
  • PaperMashup.com: Easy PHP Pagination | Webs Developer

    [...] a new post to Ashley Ford’s blog there’s a tutorial on paginating your data (from an array of data pulled from a database) with a simple PHP script: [...]

    22 December 2009 at 6:01 pm
  • thinsoldier

    What if I don’t want to paginate “everything”?

    What if I only want the results of a users search using an advanced search form?

    What if that form page submits to this paginated Results page which then builds sql for the users desired query and paginates the results?

    When they click on the “next page” how is the sql given to the next page when the sql doesn’t exist hard coded into this results page?

    What if several sections of my site are like this and all use different tables?

    What if to address the issue of passing the dynamically generated user query from one page to the next I chose to switch to having the page number in a select in a form with hidden fields containing the users original choices so that they could be posted to the next page resulting in the exact same base sql being created for the next page? I’d have to make a lot of edits that aren’t directly related to the act of pagination in a region of code full of pagination related code.

    I think it would be better to separate the display of page number links (and any display logic involved) into a separate template-like file that gets included after the main pagination & query stuff happens.

    (My personal pagination _class_ is simultaneously MUCH worse AND MUCH MUCH MUCH better.)

    $sql = “SELECT * FROM tblcountry $dynamicWhereClause ORDER BY cTitle ASC”;

    $pgn8 = new pagination($sql, 20); // 20 items per page

    echo $pgn8->showingFromTo(); // Showing 121 – 135 of 1592 Results Found

    $page_result = $pgn8->findPageResult();

    echo $links = $pgn8->displayPagination(‘pgn8-.tpl’);
    // show clickable page numbers before result list using one of the non-default markup template files (maybe this one uses roman numerals?)

    while($row = mysql_fetch_assoc($page_result))
    { loop through and display rows data as list items or table rows etc }

    echo $links; // show page numbers after the results list again if you feel like it.

    23 December 2009 at 5:09 am
  • Dean Clatworthy

    You should isolate pagination logic into it’s own class. This code is hard to reuse compared to something like:

    $pagenav = new pagenav($_REQUEST['p'], 10, $count['total'], ‘documents.php?do=overview’);

    Then the object will calculate the LIMIT values based on the parameters you pass in:

    LIMIT $pagenav->lower, $pagenav->upper

    Then of course you can render your pagenav using:

    $pagenav->render()

    I really should get around to publishing a few of these utility classes I’ve wrote. I hope it will encourage you to consider doing this for your example :)

    23 December 2009 at 2:42 pm
  • Tutorial City

    It would be very cool if you had created a simple .htaccess to display pretty URLs
    Nice tutorial ;)

    24 December 2009 at 2:11 pm
  • Easy PHP Pagination - Solution Expert Online Blogs

    [...] follow the link for more [...]

    28 December 2009 at 7:55 am
  • Adam

    Hey, this is a great tutorial! I’ve got it all working, but I want to sort my results by the date the data was submitted to the database. I’ve changed the query to SELECT * FROM dream ORDER BY `date_entered` DESC but it doesn’t seem to work, anybody know why?

    Any help would be greatly appreciated, thanks, Adam.

    5 January 2010 at 9:03 pm
  • Three Easy Steps to Becoming a Video Game Tester | Games Testing Ground

    [...] Easy PHP Pagination | Papermashup.com [...]

    15 January 2010 at 11:39 pm
  • Dude

    Nice definatly the best one i have found so far but whats the best way to display less links, i only want to show like 3-5 links maximum and the lowest i can seem to get it with your script is 7 -9 and i dont have enough room.

    26 January 2010 at 12:39 am
  • ادهم علام

    تسلم ايدك ونتمنى لك التوفيق
    مشكور الك

    15 February 2010 at 4:28 pm
  • johnm

    Easy? I don’t find this to really be much easy I guess since I’ve been using a php mvc framework for about 4 months now. It’s a great post and I thank you for your contribution. Here’s something others might want to look at using MVC frameworks.

    // function to call
    function get_limit_and_offset($per_page, $page_number)
    {
    if($page_number < 1)
    {
    $page_number = 1;
    }
    $pagination['limit'] = $per_page;
    $pagination['offset'] = ($page_number * $per_page) - $per_page;
    return $pagination;
    }
    // give function data
    $pagination_data = get_limit_and_offset($per_page, $page_number);

    // query thats used
    mysql_query("SELECT * FROM `$table_name` LIMIT $perpage OFFSET $page_num");

    17 February 2010 at 6:16 pm

Leave a comment...

Add your comment below, or trackback from your own site. You can also subscribe to these comments via RSS.

Be nice. Keep it clean. Stay on topic. No spam.

You can use these tags:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

This is a Gravatar-enabled site. To get your own globally-recognized-avatar, register at Gravatar.