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

13.3. Searching and Browsing

Example 13-3 shows the searching and browsing search.1 script used in the winestore. The script browses wines by selecting a combination of a wine region name and a wine type. For example, the script can browse the Red wines from the Margaret River region. The user can also choose to browse All regions or All wine types. The browsing interface supports Previous and Next page functionality using embedded links, as well as direct access to any page in the results.

The script is based on the browse( ) function discussed in Chapter 5, which is included here renamed as showWines( ). The showWines( ) function is customized for presenting wine details and has calls to the functions showVarieties( ) and showPricing( ) from the include.inc include file. It also has embedded links to add one or a dozen bottles of the displayed wine to the shopping cart using the cart.3 script discussed in Chapter 11.

The body of the script checks if search criteria have been provided as GET method parameters. If they have, these are used as parameters to the query that retrieves wines. If GET method parameters aren't provided, the query is configured using any previous search criteria that have been saved as the session variables sessionRegionName and sessionWineType. In either case, the current search criteria are then saved in the session variables for future use. The query string itself is created using the setupQuery( ) function.

After running the query and presenting the results with the showWines( ) function, two <select> lists containing the query parameters are presented using the selectDistinct( ) function. This function is part of the include.inc file discussed in Chapter 10, and the selectDistinct( ) function is discussed in detail in Chapter 5.

The search.1 script in Example 13-3 is the final module in the online winestore application, and this section concludes our discussion of the application.

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>


Library Navigation Links

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