Web Database Applications with PHP \& MySQLWeb Database Applications with PHP \& MySQLSearch this book

5.3. Case Study: Previous and Next Browsing

The subject of this section is a case study that uses the concepts we have discussed so far in this chapter. We show how to develop page browsing tools to display results over several pages and permit users to move between these pages. We develop this code as a generic, reusable module. A modified version of the code is used in the winestore, and the winestore browsing code is included in Chapter 13.

The aim of our case study is to show how to display large result sets in pages. Each page should be able to be displayed efficiently and viewed without using the web browser's vertical scroll bar. We also aim to make the component intuitive to use, allow direct access to any page in the results, and allow pages to be navigated using previous and next hypertext links.

We develop the module step-by-step. We begin by developing support for multiple results pages, and the previous and next links. Later in this section, we add functionality to display page numbers as links that permit direct access to a chosen page. The output of the final version of the module when it is used to browse winestore regions is shown in Figure 5-8.

Figure 5-8

Figure 5-8. A generic page browsing tool with previous and next links

In this section, as a generic page-based browser is developed, we retire the special-purpose displayWinesList( ) function completed in Example 5-6. It's replaced with a new, generic, multipurpose function browse( ). However, before we discuss how this is done, we describe what we need to achieve.

The following features are required when a user browses the wines in a region:

This can be further improved by adding page numbers to each page that allow direct access to other pages without repeatedly clicking on the previous or next links. We discuss this functionality later in this section.

5.3.1. Step 1: Using the Generic browse Function

We show how the Previous and Next links are created with PHP in the browse( ) function later, but let's return for a moment to the main body of the browsing script. Example 5-9 shows a script that uses the new generic browse( ) function to show the wines made in a region. The main segment populates several new variables that are parameters to the browse( ) function:

$pageHeader
A header for the results pages. In this case, the header is a text string Wines of, followed by the name of the region being displayed; in the example, this can create the grammatically odd Wines of All, but fixing this is outside the scope of this discussion.

$browseString
Part of the URL that is requested when the Previous and Next links are clicked. The value of $browseString is appended immediately after the ? in the URL and duplicates the variables and values passed through from the <form> displayed to the user. In Example 5-9, $browseString forms a variable and value pair such as regionName=Margaret%20River. The PHP library function rawurlencode( ) can encode spaces and other special characters in the URL.

$header
A 2D array that contains the HTML column headers and the names of the attributes to be displayed in these columns. The columns are numbered from left to right, so $header[0] is the information for the first column. We use associative access to the second element for readability: $header[0]["header"] is the text that displays at the top of the first HTML column in the <table>, while $header[0]["attrib"] is the name of the query attribute in the result set displayed in the first column.

Each column should have both a header and an attrib. The header should be human-readable text, while the attrib is the attribute name from the SELECT clause of the SQL query.

The browse() function takes these three variables—$pageHeader, $browseString, and $header—as parameters. The current $scriptName is also passed and can construct URLs for embedded links. The other parameters are the database $connection, and the $offset in the result set of the first row on the page that is displayed. The value of $offset is initially zero after running a query and, because it isn't part of the <form>, it's initialized in Example 5-9 to zero when not set. In this example, we show only the modified section of the main component of the script for preparing a query. The function browse( ), shown in Example 5-10, is called in this fragment to provide generic browsing.

Example 5-9. Adding browsing functionality to the winestore database

  // Untaint the user data
  $regionName = clean($regionName, 30);

  $scriptName = "example.5-9.php";

  // Is there any user data?
  if (empty($regionName))
  { 
     // No, so show the <form>
?>
<form action="<?=$scriptName;?>" method="GET">
  <br>Enter a region to browse :
  <input type="text" name="regionName" value="All">
  (type All to see all regions)
  <br>
  <input type="submit" value="Show wines">
</form>
<br><a href="index.html">Home</a>
<?php
   } // if user data
   else    
   {
      // Yes, there is user data so show the results
      
      // Connect to the DBMS
      if (!($connection = @ mysql_connect($hostName, 
                                        $username,
                                        $password)))
         showerror( );

      if (!mysql_select_db($databaseName, $connection))
         showerror( );

      // Set $offset to zero if not previously set
      if (empty($offset))
         $offset = 0;
    
      // Build the query 
      $query = "SELECT w.wine_id,  
                       w.wine_name,
                       w.description,
                       w.type,
                       w.year,
                       wry.winery_name
          FROM winery wry, region r, wine w
          WHERE wry.region_id = r.region_id
          AND w.winery_id = wry.winery_id";

      // Add the regionName if the user has provided it
      if ($regionName != "All")
         $query .= " AND r.region_name = \"$regionName\"";  
    
      // Add a sort on the end of the query
      $query .= " ORDER by w.wine_name";
    
      // Initialize the browse( ) function parameters
    
      // Query prefix for the next/previous links
      $browseString = "regionName=" . 
                      rawurlencode($regionName);
    
      // Page header for the browse screen
      $pageHeader = "Wines of " . $regionName;
    
      // HTML <TABLE> column headers
      $header[0]["header"] = "Wine ID";
      $header[1]["header"] = "Wine Name";
      $header[2]["header"] = "Wine Type";
      $header[3]["header"] = "Year";
      $header[4]["header"] = "Winery";
      $header[5]["header"] = "Description";
    
      // Query attributes to display in <TABLE> columns
      $header[0]["attrib"] = "wine_id";
      $header[1]["attrib"] = "wine_name";
      $header[2]["attrib"] = "type";
      $header[3]["attrib"] = "year";
      $header[4]["attrib"] = "winery_name";
      $header[5]["attrib"] = "description";
    
      // Call generic browsing code to browse query
      browse($scriptName, $connection,
             $browseString, $offset, $query, 
             $pageHeader, $header);
    
   } // end if else user data
?>
</body>
</html>

5.3.2. Step 2: Implementing the Generic browse Function

The initial implementation of the browse( ) function is shown in Example 5-10. The structure is similar to that of the hardcoded displayWinesList( ), with the additional features to display the result set page-by-page with the embedded Previous and Next links.

Example 5-10. Generic browsing code for any query

define(ROWS, 20);

// Browse through the $connection by the running $query.

// Begin the display of data with row $rowOffset.
// Put a header on the page, $pageHeader

// Use the array $header[]["header"] for headers on 
// each <table> column
// Use the array $header[]["attrib"] for the names 
// of the database attributes to show in each column

// Use $browseString to prefix an embedded link 
// to the previous, next, and other pages

function browse($scriptName,
                $connection,
                $browseString,
                $rowOffset,
                $query,
                $pageHeader,
                $header)
{

  // (1) Run the query on the database through the
  // connection
  if (!($result = @ mysql_query ($query, $connection)))
     showerror( );
     
  // Find out how many rows there are
  $rowsFound = @ mysql_num_rows($result);

  // Is there any data?
  if ($rowsFound != 0)
  {
     // Yes, there is data.

     // (2a) The "Previous" page begins at the current 
     // offset LESS the number of ROWS per page
     $previousOffset = $rowOffset - ROWS;

     // (2b) The "Next" page begins at the current offset
     // PLUS the number of ROWS per page
     $nextOffset = $rowOffset + ROWS;

     // (3) Seek to the current offset
     if (!mysql_data_seek($result, $rowOffset))
        showerror( );

     // (4a) Output the header and start a table
     echo $pageHeader;
     echo "<table border=\"1\">\n<tr>";

     // (4b) Print out the column headers from $header
     foreach ($header as $element)
        echo "\n\t<th>" . $element["header"] . "</th>";

     echo "\n</tr>";

     // (5a) Fetch one page of results (or less if on the
     // last page)
     for ( $rowCounter = 0;
         (($rowCounter < ROWS) &&
          ($row = @ mysql_fetch_array($result)) );
         $rowCounter++)
     {
        // Print out a row
        echo "\n<tr>";

        // (5b) For each of the attributes in a row
        foreach($header as $element)
        {
           echo "\n\t<td>";

           // Get the database attribute name for the
           // current attribute
           $temp = $element["attrib"];

           // Print out the value of the current
           // attribute
           echo $row["$temp"];

           echo "</td>";
        } // end foreach attribute

        echo "\n</tr>\n";
     } // end for rows in the page

     // Finish the results table, and start a footer
     echo "\n</table>\n<br>";

     // (6) Show the row numbers that are being viewed
     echo ($rowOffset + 1) .  "-" . 
          ($rowCounter + $rowOffset) . " of ";
     echo "$rowsFound records found matching " .
          "your criteria\n<br>";

     // (7a) Are there any previous pages?
     if ($rowOffset > 0)
       // Yes, so create a previous link
       echo "\n\t<a href=\"" . $scriptName . 
            "?offset=" . rawurlencode($previousOffset) .
            "&amp;" . $browseString .
            "\">Previous</a> ";
     else
       // No, there is no previous page so don't 
       // print a link
       echo "Previous ";

     // (7b) Are there any Next pages?
     if (($row != false) && ($rowsFound > $nextOffset))
       // Yes, so create a next link
       echo "\n\t<a href=\"" . $scriptName . 
            "?offset=" . rawurlencode($nextOffset) .
            "&amp;" . $browseString .
            "\">Next</a> ";
     else
       // No,  there is no next page so don't 
       // print a link
       echo "Next ";

  } // end if rowsFound != 0
  else
  {
    echo "<br>No rows found matching your criteria.\n";
  }
  // (7c) Create a link back to the query input page
  echo "<br><a href=\"" . $scriptName . 
       "\">Back to Search</a><br>";
}  

The browse( ) function performs the following steps that are numbered in the comments in Example 5-10:

  1. It runs the $query through the $connection. If there are rows returned from the query, the remaining steps are followed. If not, a message is printed.

  2. It calculates where in the result set a Previous and Next link should be relative to the current offset, $rowOffset, that was passed in as a parameter:

    // (2a) The "Previous" page begins at the current 
    // offset LESS the number of ROWS per page
    $previousOffset = $rowOffset - ROWS;
    
    // (2b) The "Next" page begins at the current offset
    // PLUS the number of ROWS per page
    $nextOffset = $rowOffset + ROWS;

    The offsets are used later to construct the Previous and Next links. ROWS is the numbers of rows per HTML page, and is defined as 20 at the beginning of Example 5-10.

  3. It then uses mysql_data_seek( ) to seek in the result set, so that a subsequent call to mysql_fetch_array( ) retrieves row number $rowOffset.

  4. The code then prints out the page header and iterates through the $header array printing out the associatively accessed "header" elements as <table> headings in the first <table> row.

  5. The script then retrieves and prints one page of rows from the result set (or, if there is less than a page of rows left to process, as many rows as are available).

    A for loop retrieves each row, and then a foreach loop prints out each attribute value in the row according to how it's listed in the $header associative array element attrib. To allow attributes to be referenced associatively by name, mysql_fetch_array( ) is used.

  6. Having printed the data in a <table>, the script prints out the range of rows displayed (from $rowOffset + 1 through $rowOffset + $rowCounter) and the total number of rows that are retrieved with the query.

  7. To conclude the function, the script produces the Previous and Next embedded links if they are required, and a Back to Search link. The previous link is created with the following code fragment:

    // Are there any previous pages?
    if ($rowOffset > 0)
       // Yes, so create a previous link
       echo "<a href=\"" . $scriptName . 
            "?offset=" . rawurlencode($previousOffset) .
            "&amp;" . $browseString .
            "\">Previous</a> ";
       else
          // No, there is no previous page so don't 
          // print a link
          echo "Previous ";

    A Previous link is produced only if the first row displayed—$rowOffset—isn't row zero; that is, we have just produced a second or later page. The code is a little cryptic, but it produces an embedded hypertext link to $scriptName, with the parameter $browseString that provides parameters to another query, and the offset variable set to the value of $previousOffset calculated earlier.

    The rawurlencode( ) function isn't strictly needed here—we are only coding a number—but consistently using it to create URLs with correctly encoded characters is good practice. The Next link is created with similar logic, and the Back to Search link is a static link to $scriptName without any parameters.

We have now developed a generic browser and applied it to browsing the wines of a region. A similar skeleton to Example 5-9 can be developed to browse customers, inventories, or orders, and all can use the generic browse( ) function.

5.3.3. Step 3: Adding Page Numbers

In this section, we extend the browse( ) function to produce page numbers to permit direct access to the pages, removing the need for the user to repeatedly click the Previous or Next links to find a particular page or row. The extended fragment of browse( ) that produces the page links is shown in Example 5-11.

Example 5-11. Adding direct page access to browse( )

// (7a) Previous link code goes here

// Output the page numbers as links
// Count through the number of pages in the results
for($x=0, $page=1;
    $x<$rowsFound; 
    $x+=ROWS, $page++)
   // Is this the current page?
   if ($x < $rowOffset || 
       $x > ($rowOffset + $numRowsToFetch - 1))
      // No, so print out a link
      echo "<a href=\"" . $scriptName . 
           "?offset=" . rawurlencode($x) .
           "&amp;" . $browseString . 
           "\">" . $page  . "</a> ";
      else
         // Yes, so don't print a link
         echo $page  . " ";

// (7b) Next link code goes here

The page number code consists of a for loop that works as follows:

The case study of a generic browse( ) function is now complete. Additional features can be added, as discussed briefly in the next section.

5.3.4. What's Missing from the Previous and Next Browser

Features that aren't described here but could be incorporated in the browse( ) function include:



Library Navigation Links

Copyright © 2003 O'Reilly & Associates. All rights reserved.