Pagination With PHP

Site Links

• Tutorials
• Web Tools
• Icons & Logos

Blogs

• Young Artist on the Move
• Quest For Clicks
• Google Gangsta
• Trip to Oregon
• Rod And Gun Club

Affiliates

• Newtuts

Pagination with PHP and MySQL

In this tutorial I will show you how to take a list of states from a database and break up the states into groups of 10 per page.

Pagination Demo

Create Database

First we need to create a database that we can extract our data from. I put up a short script you can copy from my google docs, paste it into a php file and run it to create the database table and fields.

For a copy of the database script Click Here...

Open Connection to your Database.

Since we are collecting our data from the database we need to open a connection.

<?php
// OPEN DATABASE
   define("HOSTNAME","localhost");
   define("USERNAME"," *** USERNAME HERE *** ");
   define("PASSWORD"," *** PASSWORD HERE *** ");
   define("DATABASE"," *** DATABASE HERE *** ");
 
   mysql_connect(HOSTNAME, USERNAME, PASSWORD) or die("Connetion to database failed!");
   mysql_select_db(DATABASE); 
?>

Pagination Script

In this script we are going to determine how much data we want to display per page.

<?php
// ROWS DISPLAYED PER PAGE
   $rows_per_page = 10;	
 
// GET PAGE NUMBER
   $page = $_GET['page'];
   $offset = (!empty($page)) ? $page : $page = 1;				
 
// URL CLEAN UP
   $self = $_SERVER['PHP_SELF']."?".$_SERVER['QUERY_STRING'];
   $self = str_replace("page={$offset}", "", $self);  
 
// GET LIST OF STATES
   $offset = ($page) ? ($page - 1) * $rows_per_page : 0;
   $query = "SELECT * ".
            "FROM states_list ".
            "ORDER BY id ".
            "LIMIT {$offset},{$rows_per_page}";
   $result = mysql_query($query);
 
// GET NUMBER OF PAGES
   $ltq  = mysql_query("SELECT * FROM states_list");
   $listings_total = mysql_num_rows($ltq);
   $NumPgs = ceil($listings_total / $rows_per_page);
?>
 
<div style="width: 500px; margin:auto; border:1px #666666 solid;">
   <div style="background:#CCCCCC; text-align:center; padding:4px; border-bottom:1px #666666 solid;"><strong>LIST OF STATES</strong></div>
   <?php while($row = mysql_fetch_array($result)){ ?>
   <div style="text-align:center; padding:4px; border-bottom:1px #666666 solid;"><?=$row['states']?></div>
   <?php } ?>
   <span style="float:right">
   <?=$prev = ($NumPgs > 0 && $page!=1) ? "<a href=\"{$self}page=".($page-1)."\">&lt;&lt;Prev</a>&nbsp;&nbsp;": 
   "&lt;&lt;Prev&nbsp;&nbsp;"; ?>
   [Page <?php echo $page; ?>]
   <?=$next = ($page < $NumPgs) ? "<a href=\"{$self}page=".($page+1)."\">&nbsp;&nbsp;Next&gt;&gt;</a>": 
   "&nbsp;&nbsp;Next&gt;&gt;";?>&nbsp;
   </span>			
   <b>&nbsp;<?=$offset+1?> to <?=$offset+$rows_per_page?>, of <?=$listings_total?> States</b>
</div> 
?>

Explanation

Rows Displayed Per Page / Line 3:

    $rows_per_page = 10;	

On line 3 of our pagination script we want to set the number of data we want to display per page. I put this at the top so it's easy to find for later pagination template use.

Get Page Number / Lines 6-7:

   $page = $_GET['page'];
   $offset = (!empty($page)) ? $page : $page = 1;

Since we are using URL parameters to get our page number, we need to to check the URL and see if our page param is empty or not. So, for example, if the URL to our pagination page is http://www.hollen-b.com/tutorial/pagination_demo.php we will default to page 1. If the URL to our pagination page is http://www.hollen-b.com/tutorial/pagination_demo.php?page=2 we go to page 2.

URL Clean Up / Lines 10-11:

   $self = $_SERVER['PHP_SELF']."?".$_SERVER['QUERY_STRING'];
   $self = str_replace("page={$offset}", "", $self);

Without this every time someone clicks a prev or next link our parameters will start to stack up in our URL. So we need to get the URL and remove the old page parameter.

Get List Of States / Lines 14-18:

   $offset = ($page) ? ($page - 1) * $rows_per_page : 0;
   $query = "SELECT * ".
            "FROM states_list ".
            "ORDER BY id ".
            "LIMIT {$offset},{$rows_per_page}";
   $result = mysql_query($query);

Every time we load the page we are going to run two arguments to MySQL using LIMIT X, Y. X is the starting point, and Y is the total amount of records we want to display. To get X in our case is $offset, we need to first get the page number were on. Subtract the page number by 1, multiply that number by the total rows per page and we now should have the X value we need. Y is simply our number of rows we want to display which was set on line 3.

Get Number Of Pages / Line 22-24:

   $ltq  = mysql_query("SELECT * FROM states_list");
   $listings_total = mysql_num_rows($ltq);
   $NumPgs = ceil($listings_total / $rows_per_page);

If we query the database for our total number of rows, we can divide that by the total rows per page, which should give us our total number of pages. By using ceil function we can round our total number of pages to the next highest integer.