home | O'Reilly's CD bookshelfs | FreeBSD | Linux | Cisco | Cisco Exam  

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:

  • Only one page of wineries is shown at a time. When the user runs the query, only the first 20 rows of results are shown.

  • As in Figure 5-8, an embedded Next link displays that allows the user to move to the next page of rows. If the user is accessing the first page, the Next link runs a query that shows the second page of results; that is, rows 21 to 40.

  • When the user reaches the last page of results—which usually has less than 20 rows—the Next link is hidden.

  • An embedded Previous link is shown that moves backward through the pages.

  • The Previous link is hidden when the first page is displayed.

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:

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.

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.

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)
  <input type="submit" value="Show wines">
<br><a href="index.html">Home</a>
   } // if user data
      // Yes, there is user data so show the results
      // Connect to the DBMS
      if (!($connection = @ mysql_connect($hostName, 
         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,  
          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=" . 
      // 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

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,

  // (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)) );
        // 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> ";
       // 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> ";
       // No,  there is no next page so don't 
       // print a link
       echo "Next ";

  } // end if rowsFound != 0
    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> ";
          // 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+=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> ";
         // 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 loop begins counting rows using the variable $x—starting at row zero—and pages using $page, starting on page one. The loop finishes when $x is equal to the number of rows in the query result set.

  • In the body of the loop, if the row $x isn't on the current page displayed in the HTML <table>, an embedded link is output that is marked with the page number $page. The link is to the script resource $scriptName, with the parameters in $browseString and an offset of the current value of $x. The current value of $x is the first row on the page numbered $page. Clicking on the link requests the script again and produces the results for $page that begin with the row with an offset of $x.

    For example, if $x is row 220, and the $page is 11, the embedded link output by the code fragment is:

    <a href= "example.5-11.php? offset=220&amp;regionName=Margaret%20River">11</a>
  • If $x is a row on the currently displayed page, the code outputs the page number without the embedded hypertext link.

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

Library Navigation Links

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