PHP

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="Preface.htmll">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>