How to Do Paging in PHP

Database driven websites, no matter what scripting or database language combination one uses, all have a common problem: how to display the massive amounts of data without creating a webpage long enough to wrap around the moon. The solution to this problem is to break each set of information into individual pages, known simply as pagination.

Instructions

Connection and Variables

1. 

   Connect to your database. You can use the following script:
   $dbh=mysql_connect ("localhost", "USERNAME", "PASSWORD") or die ('I cannot connect to the database because: ' . mysql_error());
   Replace USERNAME with your username, PASSWORD with your password, and DATABASE with the data that you are extracting.

2. 

   The concept of pagination is essentially breaking up, or limiting the data into smaller chunks so that the end user can digest what you are presenting.
   First establish how many rows of data from your database that you want to present to the user, per page. Establish the variable, $rowsPerPage and set it to the number of records you wish to display.
   // how many rows to show per page
   $rowsPerPage = 1;

3. 

   Establish that the first page displayed is page 1. Establish the variable and assign it to the number 1.
   // by default we show first page
   $pageNum = 1;

4. 

   Then, create an if statement to 'GET' the page number, assuming that it is defined. We are using the function isset() to check if 'page' has been established. Assign the result to the $pageNum variable from Step 3:
   // if $_GET['page'] defined, use it as page number
   if(isset($_GET['page']))
   {
   $pageNum = $_GET['page'];
   }

5. 

   The final part of Section 1 is creating the offset. When limiting a query to a database, two numbers can be used, which looks like this: LIMIT 0,10. The $offset variable is what record number the query starts from, in this case '0', and the rows per page, in this case '10', is how many records are to be listed until the query ends.
   // counting the offset
   $offset = ($pageNum - 1) * $rowsPerPage;

Query and Paging

1. 

   Create the query and list the results.
   // Select Query
   $query = mysql_query("SELECT * FROM database.table LIMIT $offset, $rowsPerPage",$dbh);
   while($r=mysql_fetch_array($query)) {
   extract($r);
   echo $variables;
   }
   Substitute database.table for your database name and table name. Also include any ordering instructions and clauses necessary to display the information. Then, LIMIT the results using the variables $offset and $rowsPerPage, separated by a comma. Replace variables with the column names from your database.

2. 

   The next step is to count the total number of records in your database that fit the criteria in the query created in Step 1:
   // how many rows we have in database
   $query = "SELECT COUNT(columnName) AS numrows FROM database.table ";
   $result = mysql_query($query) or die('Error, query failed Part 2');
   $row = mysql_fetch_array($result, MYSQL_ASSOC);
   $numrows = $row['numrows'];
   Replace columnName with the column that is most important to your data set. Replace database.table with your database name and table name.

3. 

   Establish the variable $maxPage, and set it equal to the number of rows that fit your criteria, and divide it by the number of rows of data displayed per page. Enclose this with the ceil() function to eliminate any decimals.
   // how many pages we have when using paging?
   $maxPage = ceil($numrows/$rowsPerPage);

4. 

   Create the navigation links, including 'First Page', 'Last Page', 'Next', and 'Previous', links to each numbered page.
   // print the link to access each page
   $self = $_SERVER['PHP_SELF'];
   $nav = '';
   for($page = 1; $page <= $maxPage; $page++)
   {
   if ($page == $pageNum)
   {
   $nav .= " $page "; // no need to create a link to current page
   }
   else
   {
   $nav .= " <a href=\"$self?page=$page\">$page</a> ";
   }
   }
   // creating previous and next link
   // plus the link to go straight to
   // the first and last page
   if ($pageNum > 1)
   {
   $page = $pageNum - 1;
   $prev = " <a href=\"$self?page=$page\">[Prev]</a> ";
   $first = " <a href=\"$self?page=1\">[First Page]</a> ";
   }
   else
   {
   $prev = ' '; // we're on page one, don't print previous link
   $first = ' '; // nor the first page link
   }
   if ($pageNum < $maxPage)
   {
   $page = $pageNum + 1;
   $next = " <a href=\"$self?page=$page\">[Next]</a> ";
   $last = " <a href=\"$self?page=$maxPage\">[Last Page]</a> ";
   }
   else
   {
   $next = ' '; // we're on the last page, don't print next link
   $last = ' '; // nor the last page link
   }

5. 

   Finally, list the variables that stand for the navigation:
   echo $nav . "<br />";
   echo $next . "  " . $prev . "<br />";
   echo $first . "  " . $last;
   Close the connection to the database.
   mysql_close();

Tips & Warnings

• In Section 2, Step 5, you can alter where the navigation links are displayed by enclosing the various sections with <div></div> tags and assigning CSS rules to them.

• CSS classes can be assigned to the $first, $last, $prev, and $next variables by adding a class to the <a> tags where you see the [Next], [Previous], [First Page], and [Last Page] text in Section 2, Step 4.