Example 13-3. search.1 searches and browses wines in the winestore

<?php
   // This is the script that allows the to search and
   // browse wines, and to select wines to add to their
   // shopping cart
   
   include 'include.inc';

   set_error_handler("errorHandler");

   // Show the user the wines that match their query
   // This is a modified version of the browse( ) function 
   // from Chapter 5
   function showWines($query, 
                      $connection, 
                      $offset, 
                      $scriptName, 
                      $browseString)
   {
      // Number of rows per page
      $ROWS = 12;

      // 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.

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

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

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

         // Output the header and start a table
         echo "<table border=\"0\">\n";

         // Fetch one page of results (or less if on the
         // last page)
         for ( $rowCounter = 0;
             (($rowCounter < $ROWS) &&
              ($row = @ mysql_fetch_array($result)) );
             $rowCounter++)
         {
            echo "\n<tr>\n\t<td>" . $row["year"] . " " .
                 $row["winery_name"] . " " .
                 $row["wine_name"];

            // Print the varieties for this wine
            echo showVarieties($connection,
                               $row["wine_id"]);     

            // Print out the pricing information
            echo "\n\t<br>";
            showPricing($connection, $row["wine_id"]);

            echo "</td>";

            // Show the single-bottle add to cart link
            echo "\n\t<td><a href=\"example.cart.3.php?" .
                 "qty=1&amp;wineId=" .
                 $row["wine_id"] .
                 "\">Add a bottle to the cart</a></td>";  

            // Show the dozen add to cart link
            echo "\n\t<td><a href=\"example.cart.3.php?" .
                 "qty=12&amp;wineId=" .
                 $row["wine_id"] .
                 "\">Add a dozen</a></td>";  

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

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

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

         // Are there any previous pages?
         if ($offset > 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 ";
         // 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 < $offset || $x > ($offset + $ROWS - 1))
             // No, so print out a link
             echo "\n<a href=\"" . $scriptName . 
                  "?offset=" . rawurlencode($x) .
                "&amp;" . $browseString .
                  "\">" . $page  . "</a> ";
           else
             // Yes, so don't print a link
             echo "\n" . $page  . " ";

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

      } // end if rowsFound != 0
      else
      {
         echo "\n<br>No wines found matching your " .
              " criteria.\n";
      }
   }

   function setupQuery($regionName, $wineType)
   {

      // Show the wines stocked at the winestore that match
      // the search criteria
      $query = "SELECT DISTINCT wi.winery_name, 
                        w.year, 
                        w.wine_name, 
                        w.wine_id
                FROM wine w, winery wi, 
                     inventory i, region r
                WHERE w.winery_id = wi.winery_id
                AND wi.region_id = r.region_id
                AND w.wine_id = i.wine_id";

      // Add region_name restriction if they've 
      // selected a search parameter
      if ($regionName != "All")
         $query .= " AND r.region_name = \"" . 
                   $regionName . "\"" . 
                   " AND r.region_id = wi.region_id";
         
      // Add wine type restriction if they've selected 
      // a search parameter
      if ($wineType != "All")
         $query .= " AND w.type = \"" . 
                   $wineType . "\"";

      // Add sorting criteria
      $query .= " ORDER BY wi.winery_name, " .
                "w.wine_name, w.year";
 
      return ($query);
   }

   // ---------
   
   // Initialize the session
   session_start( );    

   // Process the search parameters. 

   // If a regionName is passed as a GET parameter,
   // use it. Otherwise, load the session variable
   // from the last search. If there is no previous
   // search and no parameter, set search to "All"
   if (!empty($HTTP_GET_VARS["regionName"]))
      $regionName = clean($regionName, 30);
   elseif (session_is_registered("sessionRegionName"))
      $regionName = $sessionRegionName;
   else 
      $regionName = "All";

   // Load wineType, using the same approach as
   // regionName
   if (!empty($HTTP_GET_VARS["wineType"]))
      $wineType = clean($wineType, 20);  
   elseif (session_is_registered("sessionWineType"))
      $wineType = $sessionWineType;
   else
      $wineType = "All";

   // Load offset
   if (!empty($HTTP_GET_VARS["offset"]))
      $offset = clean($offset, 5);  
   else
      $offset = 0;

   // Save the search criteria
   $sessionRegionName = $regionName;
   $sessionWineType = $wineType;

   // Register the search criteria if needed
   if (!session_is_registered("sessionRegionName"))
   {
      session_register("sessionRegionName");
      session_register("sessionWineType");
   }

   // Open a connection to the DBMS
   if (!($connection = @ mysql_pconnect($hostName,
                                       $username,
                                       $password)))
      showerror( );

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

   // Build the query using the search criteria
   $query = setupQuery($regionName, $wineType);

   // This is used to encode the search parameters 
   // for embedding in links to other pages of results
   $browseString = "wineType=" .
                   urlencode($wineType) .
                   "&amp;regionName=" .
                   urlencode($regionName);

   $scriptName = "example.search.1.php";

?>
<!DOCTYPE HTML PUBLIC
  "-//W3C//DTD HTML 4.01 Transitional//EN"
  "http://www.w3.org/TR/html401/loose.dtd">
<html>
<head>
  <title>Hugh and Dave's Online Wines</title>
</head>
<body bgcolor="white">
<?php
   // Show the user login status
   showLogin( );

   // Show the dollar and item total of the cart
   showCart($connection);

   // Show a meaningful heading that describes the
   // search criteria
   echo "<h1>" . $wineType . " wines";

   if ($regionName == "All")
      echo " from all regions.";
   else
      echo " of the " . $regionName . " region.";

   echo "</h1>\n";

   // Display any messages to the user
   showMessage( );

   // Show the user their search
   showWines($query, $connection, 
             $offset, $scriptName, 
             $browseString);

   echo "<form action=\"example.cart.5.php\"" .
        " method=\"GET\">\n";
   echo "<table>\n<tr>\n";

   echo "\t<td>Choose a wine region:</td>\n\t<td>";
   // Produce a select list of wine regions
   selectDistinct($connection,
                  "region",
                  "region_name",
                  "regionName",
                  "All",
                  $regionName);

   echo "</td>\n</tr>\n<tr>\n";

   echo "\t<td>Choose a wine type:</td>\n\t<td>";
   // Produce a select list of wine types
   selectDistinct($connection,
                  "wine",
                  "type",
                  "wineType",
                  "All",
                  $wineType);

   echo "</tr>\n</table>\n";

   echo "<table>\n<tr>\n";

   // Show the user the search screen button
   echo "\t<td><input type=\"submit\" " .
        "name=\"search\" value=\"Search\"></td>\n";

   // Show the user the search screen button
   echo "\t<td><input type=\"submit\" " .
        "name=\"home\" value=\"Home\"></td>\n";

   // If the cart has contents, offer the opportunity 
   // to view the cart or empty the cart.
   if (session_is_registered("order_no"))
   {
      echo "\t<td><input type=\"submit\" " .
           "name=\"empty\" value=\"Empty Cart\"></td>\n";
      echo "\t<td><input type=\"submit\" " .
           "name=\"view\" value=\"View Cart\"></td>\n";
   }

   // Show the user either a login or logout button
   loginButtons( );

   echo "\n</tr>\n</table>\n";
   echo "</form>\n";
?>
<br><a href="http://validator.w3.org/check/referer">
    <img src="http://www.w3.org/Icons/valid-html401" 
     height="31" width="88" align="right" border="0"
     alt="Valid HTML 4.01!"></a>
</body>
</html>