Chapter 2:
Data Paging

Right now, when we display an individual recipe in our application, we also display any comments that visitors have posted about it. For a small Web site, that probably wouldn't be a problem. But if this were a large Web site with lots of visitors, each recipe could have hundreds of comments posted about it. The last thing we want to do is dump hundreds of comments onto a single Web page. Doing that would have three bad effects. It would:

1. Make the data presentation confusing and often hard to follow
2. Force visitors to wait until all of the comments load onto the Web page (which could take awhile on slow Internet connections)
3. Needlessly increase the network traffic required to serve the Web page to the visitor

If your ISP charges you for your specific bandwidth usage (or even if you just have a bandwidth limitation), you don't want to dump lots of data that visitors may not even read. The smart thing to do is provide visitors with a way to easily sort through the data by looking at only the information they're interested in.

Data paging provides a great way to control data flow on a Web site. It's a method of breaking data into manageable chunks and presenting each chunk individually to visitors, one Web page at a time. You provide links that allow visitors to select additional chunks if they want to see more information.

If you've used any of the standard search sites, such as Google or Yahoo, you've seen paging in action. When you search for a term, the search site returns a Web page with a predetermined number of search results. At the bottom of the Web page, you see a list of navigation links that point to additional pages.

Basically, the search site splits the returned search results into several pages, each containing a section of the results. You can select to go to a specific page number or single-step through the pages by clicking a Next or Previous link.

Our goal is to implement this feature within the recipe comments section of the Recipe Center application. To do that, we'll need to add some SQL statements and extra PHP code to our application. First, let's look at the SQL statements we'll need.

Paging With SQL

Throughout this course, we've been using the SQL SELECT statement to query the MySQL database to retrieve data records. The specific SQL SELECT statement we use to retrieve the recipe comments that we display looks like this:

SELECT date,poster,comment FROM comments WHERE recipeid = $recipeid ORDER BY commentid DESC


This SQL statement retrieves all the comment records where the value of the recipeid data field is equal to the $recipeid PHP variable, then it sorts them in descending order by the commentid data field value.

If there are 100 comment records related to a recipe, this SQL statement will retrieve all 100 records in the result set. The trick behind paging is to return only a subset of the total result set we'd receive from this query. For example, instead of returning all 100 records at once, with paging you can return just the first 10 records to display on a Web page and then retrieve the next 10 to display on another Web page, and so on until you've displayed all 100 records. The easiest way to do that is to limit the number of records the SELECT statement returns and then be able to go back for more records where we left off.

We've already seen from the main.inc.php PHP include file how to limit the number of records we retrieve from a SELECT statement using the LIMIT clause:

SELECT recipeid,title,poster,shortdesc FROM recipes ORDER BY recipeid DESC LIMIT 0,5


The LIMIT clause specifies the starting offset point in the returned query result set and the number of records of the query result set to return. The offset point indicates the number of records in the result set to skip before displaying records. The values 0,5 tell the SELECT statement to start at the first record in the result set (it starts at position zero) and return only the first five records of the result. Any additional records are ignored. The values 10,5 tell the SELECT statement to skip the first 10 records in the result set, then display the next five records.

The trick to paging is to set the LIMIT offset value to retrieve just a specific number of records for a single Web page, move the offset number to the next group of records, and then display the next group of records on the next Web page. You just need to keep moving the offset value until you've retrieved the entire result set.

This technique requires that we step our way through the query result set, one group of records at a time. Unfortunately, this can get confusing. Here's a table that demonstrates what I mean.

This table demonstrates retrieving five records at a time from a 30 record result set. For the 30 records, it takes six offset values to return the entire result set. As you can see, the offset values don't match what you would normally consider as the next record in a group. For example, offset value 20 actually starts at record number 21 (whose idea was it to decide to start counting at record zero?). Because of this, we'll need to do some math to determine the appropriate offset value for each page we want to display.

Doing the Math

The first piece of information we'll need for our calculations is the total number of records that are in the returned result set. We'll use this value to calculate how many offset groups we need to process. This can be found using the count() SQL function:

$query = "SELECT count(commentid) FROM comments WHERE recipeid = $recipeid";
$result=mysql_query($query);
$row=mysql_fetch_array($result);


The array variable value $row [0] now contains the total number of records to expect in the result set. Now we can determine how many pages of data we'll need, given a set number of records per page.

To determine the total number of pages we'll need, we must set the number of records we want to display per page and then use the formula:

$recordsperpage = 5;
$totalpages = ceil($row [0] / $recordsperpage);


The $recordsperpage variable holds the number of records we want to display on a single page. The $totalpages variable must be an integer value (you can't display 2.4 pages of data), so we'll need to round this value up. We'll use the ceil() PHP function to do that. The ceil() function returns the next highest integer for a value. Thus, the function ceil(2.4) returns the value 3.

Now we know the total number of pages we'll need to display all of the data. To find the required LIMIT offset value for a specific page number, we have to use this formula:

$offset = ($thispage - 1) * $recordsperpage;


The $thispage variable defines the page number the visitor wants to view. I subtract one from that value so we can present data pages to our visitors starting at page one rather than the silly offset zero (who starts reading at page zero?).

That's all the math we'll need to do for this project (not too painful, was it?). Now we can move on to Chapter 3 and start formulating some PHP code.

Chapter 3:
Creating PHP Code for Paging

Now that you've seen the formulas we need to implement, let's start putting together the code required to put paging into action.

To implement paging, we'll need to keep track of what page of comments a visitor is viewing and what page he or she wants to display next. We'll use an HTML variable called page to do that.

We'll add that variable to the links we use to display the showrecipe.inc.php PHP include file. To make life easier for us, we'll assume that if the variable is not set, we should start at page one:

if (!isset($_GET['page']))
$thispage = 1;
else
$thispage = $_GET['page'];

If the variable is set, we assign the value to the $thispage PHP variable for use later in the code.

Now that we know what page we're on, we can calculate our LIMIT values, submit the SELECT statement, and display the comment records:

Print code

$recordsperpage = 5;
$offset = ($thispage - 1) * $recordsperpage;
$totpages = ceil($totrecords / $recordsperpage);
$query = "SELECT date,poster,comment FROM comments WHERE recipeid = $recipeid ORDER BY commentid DESC LIMIT $offset,$recordsperpage";
$result = mysql_query($query) or die('Could not retrieve comments');
while($row = mysql_fetch_array($result, MYSQL_ASSOC))
{
    $date = $row['date'];
    $poster = $row['poster'];
    $comment = $row['comment'];
    $comment = nl2br($comment);
    echo $date . " - posted by " . $poster . "\n";
    echo "<br>\n";
    echo $comment . "\n";
    echo "<br><br>\n";
}

• A link to the previous page (if we're not on the first page)
• Individual links for each page
• A link to the next page (if we're not on the last page)

The navigation bar should look something like this:

A generic paging navigation bar

Let's tackle each of these elements in order to build our navigation bar piece by piece.

Building the Previous Page Link

The Previous Page link is an HTML link that allows the visitor to go to the previous page from the one currently being displayed. If the visitor is currently on the first page, then we'll disable the link. Here's the code we'll use:

Print code

if ($thispage > 1)
   {
      $page = $thispage - 1;
      $prevpage = "<a href=\"index.php?content=showrecipe&id=$recipeid&page=$page\">Previous</a>";
   } else
   {
      $prevpage = "Previous";
   }

This is not too complicated. The HTML link sets the content, id, and page HTML variables with the data we'll need to display the previous comments page. The id variable is taken from the existing $recipeid value for the Web page. If we're already on the first page of data, we'll just display the word Previous without making it a link.

Building the Individual Page Links

After the Previous page link, we need to list each of the available data Web pages as links in the navigation bar. You can do this using this code:

Print code

if ($totpages > 1)
{ 
    $bar = '';
    for($page = 1; $page <= $totpages; $page++)
    {
        if ($page == $thispage)      
       {
           $bar .= " $page ";
       } else
       {
          $bar .= " <a href=\"index.php?content=showrecipe&id=$recipeid&page=$page\">$page</a> ";
       }
    }
}

If there is only one page of data, we won't bother with any page links. So in the code above, the if statement ensures we only run this code if there is more than one page of data. Next, we clear out the $bar variable to start with a blank list of pages.

The for statement steps through once for each page number. If the page is the current page we're viewing, we won't make it a link, but just use its number as a placeholder. We add it to the $bar variable using the PHP string dot operator. The dot operator is an easy way to append a string value to an existing string. We'll be using this feature to build our navigation bar one piece at a time.

If the page isn't the current page, we need to add the HTML link for the page to the $bar variable, again using the PHP string dot operator. The HTML link must include the content and recipeid HTML variables to ensure we display the correct recipe with our comments.

The Next Page Link

The final element is the link to the next page in the series. This is almost exactly like the code for the Previous page link, except now we're looking for the last page instead of the first:

Print code

if ($thispage < $totpages)
{
      $page = $thispage + 1;
      $nextpage = " <a href=\"index.php?content=showrecipe&id=$recipeid&page=$page\">Next</a>";
} else
{
      $nextpage = "Next";
}

You should recognize everything here. Again, if we're already on the last page ($totpages), we'll just display the word Next and not make it a link.

Now that we have all of the elements, we can put them together to display the navigation bar:

echo "GoTo: " . $prevpage . $bar . $nextpage;

And that's all there is to it. The final result will look like this:

The finished comment page navigation bar

Now let's put this code into the showrecipe.inc.php PHP include file and see what happens.

Chapter 4:
Adding Paging to the Recipe Center

All of the code we need to add goes in the showrecipe.inc.php file. Here's what the final code will look like once we've made the addition:

Print code

<?php
$con = mysql_connect("localhost", "test", "test") or die('Could not connect to server ');
mysql_select_db("recipe", $con) or die('Could not connect to database');
$recipeid = $_GET['id'];
$query = "SELECT title,poster,shortdesc,ingredients,directions from recipes where recipeid = $recipeid";
$result = mysql_query($query) or die('Could not find recipe');
$row = mysql_fetch_array($result, MYSQL_ASSOC) or die('No records retrieved');
$title = $row['title'];
$poster = $row['poster'];
$shortdesc = $row['shortdesc'];
$ingredients = $row['ingredients'];
$directions = $row['directions'];
$ingredients = nl2br($ingredients);
$directions = nl2br($directions);
echo "<h2>$title</h2>\n";
echo "by $poster <br><br>\n";
echo $shortdesc . "<br><br>\n";
echo "<h3>Ingredients:</h3>\n";
echo $ingredients . "<br><br>\n";
echo "<h3>Directions:</h3>\n";
echo $directions . "\n";
echo "<br><br>\n";
$query = "SELECT count(commentid) from comments where recipeid = $recipeid";
$result = mysql_query($query);
$row=mysql_fetch_array($result);
if ($row[0] == 0)
{
   echo "No comments posted yet.  \n";
   echo "<a href=\"index.php?content=newcomment&id=$recipeid\">Add a comment</a>\n";
   echo "   <a href=\"print.php?id=$recipeid\" target=\"_blank\">Print recipe</a>\n";
   echo "<hr>\n";
} else
{
   $totrecords = $row[0];
   echo $row[0] . "\n";
   echo " comments posted.  \n";
   echo "<a href=\"index.php?content=newcomment&id=$recipeid\">Add a comment</a>\n";
   echo "   <a href=\"print.php?id=$recipeid\" target=\"_blank\">Print recipe</a>\n";
   echo "<hr>\n";
   echo "<h2>Comments:</h2>\n";
   if (!isset($_GET['page']))
      $thispage = 1;
   else
      $thispage = $_GET['page'];
   $recordsperpage = 5;
   $offset = ($thispage - 1) * $recordsperpage;
   $totpages = ceil($totrecords / $recordsperpage);
   $query = "SELECT date,poster,comment from comments where recipeid = $recipeid order by commentid desc limit $offset,$recordsperpage";
   $result = mysql_query($query) or die('Could not retrieve comments: ' . mysql_error());
   while($row = mysql_fetch_array($result, MYSQL_ASSOC))
   {
       $date = $row['date'];
       $poster = $row['poster'];
       $comment = $row['comment'];
       $comment = nl2br($comment);
       echo $date . " - posted by " . $poster . "\n";
       echo "<br>\n";
       echo $comment . "\n";
       echo "<br><br>\n";
   }
   if ($thispage > 1)
   {
      $page = $thispage - 1;
      $prevpage = "<a href=\"index.php?content=showrecipe&id=$recipeid&page=$page\">Prev</a>";
   } else
   {
      $prevpage = " ";
   }
   if ($totpages > 1)
   { 
      $bar = '';
      for($page = 1; $page <= $totpages; $page++)
      {
         if ($page == $thispage)      
         {
            $bar .= " $page ";
         } else
         {
            $bar .= " <a href=\"index.php?content=showrecipe&id=$recipeid&page=$page\">$page</a> ";
         }
      }
   }
   if ($thispage < $totpages)
   {
      $page = $thispage + 1;
      $nextpage = " <a href=\"index.php?content=showrecipe&id=$recipeid&page=$page\">Next</a>";
   } else
   {
      $nextpage = " ";
   }
   echo "GoTo: " . $prevpage . $bar . $nextpage;
}
?>

After displaying the selected recipe, we use the SELECT statement to determine if there are any comments to display. If there are comments, we display a couple of HTML links to add a new comment and to print the comments, and then we implement the new paging code we discussed in Chapter 3. We build each element of the paging navigation bar and then display the bar at the end of the comments.

If you want to enable paging in your comments section, replace the existing showrecipe.inc.php file with the code shown above. You can test this out by adding a bunch of comments to a recipe and seeing how the new code displays them.

Getting Fancier

This paging solution provides an easy navigation area for visitors to page through the result set Web pages. However, it has one drawback. If there are lots of pages to display, you see all the page numbers listed along the bottom of the Web page. This can get ugly pretty quickly (just think what it would look like if there were 100 pages of data).

There are quite a few solutions to this problem. Just taking a cursory look at some of the more popular commercial sites should give you some ideas of what to implement in your application. Some sites just provide the Previous and Next links, along with indicating the current page number. While this cuts down on the clutter, it prevents visitors from jumping directly to a specific data page.

Other sites provide a textbox for visitors to enter a desired page number and a Go button that jumps directly to that page. This method requires just a simple HTML form that passes the page number as an HTML variable using the GET method:

Print code

echo "Displaying page $thispage of $totpages";
   echo "<form action=\"index.php\" method=\"get\">";
   echo "<input type=\"hidden\" name=\"content\" value=\"showrecipe\">";
   echo "<input type=\"hidden\" name=\"id\" value=$recipeid>";
   echo "Jump to Page:<input type=\"text\" size=\"2\" name=\"page\">";
   echo "<input type=\"submit\" value=\"Go\">";

You can use this PHP code to replace the code that builds the navigation bar for the comments. The hidden fields are important, as you need to remember to pass along the content and ID HTML variables.

There are lots of other ways to implement paging in a dynamic Web application. Try experimenting with different techniques and see which ones you like best.

Once you've calculated all of your necessary paging variables, you can use them in a variety of ways to get your visitor to the correct data page.

Chapter 5:
Summary

Today, we implemented yet another advanced feature in the Recipe Center application. Data paging is a great feature if you have lots of information to display but don't want to dump it on the visitor all at once.

To implement paging, we examined how to customize the SQL SELECT statement using the LIMIT clause. We saw how to set the offset value to step our way through the result set data one group of records at a time.

Next, we worked out the PHP code necessary to display each group of records within a Web page and create a navigation bar that allows visitors to either step through the data groups or just go directly to a specific Web page of information.

That finishes the Recipe Center application. It's amazing how much ground we've covered while creating a commercial-quality content management system! Now, just to be sure you come away from this course with a fully-functioning system, here's a link to download the complete code:

Download the complete Recipe Center code

Of course, even when you get your application working on the Web, your work still isn't done. When you're responsible for an application, you must ensure that it continues to function properly, and you have to know what to do if things go wrong. So in the final lesson of this course, we'll examine the administrative tasks involved in keeping a Web application running smoothly.

Supplementary Material

PHP Paging Class http://www.phpclasses.org/browse/package/202.html

Using PHP to page through data records is an extremely common feature. This site contains prebuilt PHP classes for lots of functions. This link demonstrates a PHP paging code class you can use in a variety of applications.

FAQs

Q: Are there other ways of presenting the page links in the navigation bar?

A: Yes. For large quantities of data, it's often impractical to list all of the page numbers. Another popular method is to list groups of page numbers, allowing visitors to select a range of pages to visit. Of course, you can always create a text box to allow visitors to manually enter a specific page number to jump to.

Assignment

In Lesson 11, you've seen how to implement data paging in the recipe comments section. The front page of the Recipe Center application limits the number of recipes it displays to just the last five recipes. Try changing the PHP code in the main.inc.php file to incorporate paging so visitors can page through all of the recipes posted.

Remember that you'll need to create an HTML variable to track the page number and then pass that variable along in your HTML links. You can set the content HTML variable to main so that it directly calls the main.inc.php file.