Previous | Table of Contents | Next |
20.2 Searching and BrowsingThis section describes the searching and browsing module in the winestore. As the scripts are complex, we've divided the discussions into three parts: a short description of the search criteria input form; a discussion of the SQL query used to retrieve matching wines; and, an longer overview of the code that produces the browsable results. 20.2.1 Search Criteria FormExample 20-1 lists the search input criteria search/searchform.php script. The script is a straightforward use of the winestoreFormTemplate class discussed in Chapter 16. It allows users to choose a region and a wine type to browse, and uses the winestoreFormTemplate::selectWidget( ) method to present these as drop-down lists. Example 20-1. Thesearch/searchform.php script that displays a search criteria entry form<?php // This is the script that allows the to search and browse wines, and // to select wines to add to their shopping cart require_once "../includes/template.inc"; require_once "../includes/winestore.inc"; set_error_handler("customHandler"); session_start( ); // Takes <form> heading, instructions, action, formVars name, and // formErrors name as parameters $template = new winestoreFormTemplate("Search", "Choose regions and wine types to browse.", S_SEARCH, "searchFormVars", NULL, "GET"); $connection = DB::connect($dsn, true); if (DB::isError($connection)) trigger_error($connection->getMessage( ), E_USER_ERROR); // Create the drop-down search widgets for the page // Load the regions from the region table $regionResult = $connection->query("SELECT * FROM region"); if (DB::isError($regionResult)) trigger_error($regionResult->getMessage( ), E_USER_ERROR); // Load the wine types from the wine_type table $wineTypeResult = $connection->query("SELECT * FROM wine_type"); if (DB::isError($wineTypeResult)) trigger_error($wineTypeResult->getMessage( ), E_USER_ERROR); $template->selectWidget("region_name", "Region name:", "region_name", $regionResult); $template->selectWidget("wine_type", "Wine type:", "wine_type", $wineTypeResult); $template->showWinestore(NO_CART, B_HOME | B_SHOW_CART | B_LOGINLOGOUT); ?> 20.2.2 Querying and Displaying ResultsExample 20-2 lists the complex search/search.php script that performs the search. The script retrieves wines that match the user-supplied combination of wine region name and wine type, and displays the results in pages of twelve wines each. For example, the script can be used to browse the Red wines from the Margaret River region, and to view the 38 matching wines over 4 result pages. Example 20-2. The search/search.php script that displays wines in pages<?php // This is the script that allows the to search and browse wines, and // to select wines to add to their shopping cart require_once "DB.php"; require_once "../includes/template.inc"; require_once "../includes/winestore.inc"; set_error_handler("customHandler"); // Construct the query function setupQuery($region_name, $wine_type) { // 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, wine_type wt WHERE w.winery_id = wi.winery_id AND w.wine_id = i.wine_id"; // Add region_name restriction if they've selected anything // except "All" if ($region_name != "All") $query .= " AND r.region_name = '{$region_name}' AND r.region_id = wi.region_id"; // Add wine type restriction if they've selected anything // except "All" if ($wine_type != "All") $query .= " AND wt.wine_type = '{$wine_type}' AND wt.wine_type_id = w.wine_type"; // Add sorting criteria $query .= " ORDER BY wi.winery_name, w.wine_name, w.year"; return ($query); } // Show the user the wines that match their query function showWines($connection, &$template) { // Produce a heading for the top of the page $template->setCurrentBlock( ); $template->setVariable("SEARCHCRITERIA", "Region: {$_SESSION["searchFormVars"]["region_name"]} " . "Wine type: {$_SESSION["searchFormVars"]["wine_type"]}"); // Encode the search parameters for embedding in links to other pages // of results $browseString = "wine_type=" . urlencode($_SESSION["searchFormVars"]["wine_type"]) . "&region_name=" . urlencode($_SESSION["searchFormVars"]["region_name"]); // Build the query using the search criteria $query = setupQuery($_SESSION["searchFormVars"]["region_name"], $_SESSION["searchFormVars"]["wine_type"]); $result = $connection->query($query); if (DB::isError($result)) trigger_error($result->getMessage( ), E_USER_ERROR); $numRows = $result->numRows( ); // Is there any data? if ($numRows > 0) { // Yes, there is data. // Check that the offset is sensible and, if not, fix it. // Offset greater than the number of rows? // Set it to the number of rows LESS SEARCH_ROWS if ($_SESSION["searchFormVars"]["offset"] > $numRows) $_SESSION["searchFormVars"]["offset"] = $numRows - SEARCH_ROWS; // Offset less than zero? Set it to zero if ($_SESSION["searchFormVars"]["offset"] < 0) $_SESSION["searchFormVars"]["offset"] = 0; // The "Previous" page begins at the current // offset LESS the number of SEARCH_ROWS per page $previousOffset = $_SESSION["searchFormVars"]["offset"] - SEARCH_ROWS; // The "Next" page begins at the current offset // PLUS the number of SEARCH_ROWS per page $nextOffset = $_SESSION["searchFormVars"]["offset"] + SEARCH_ROWS; // Fetch one page of results (or less if on the // last page, starting at $_SESSION["searchFormVars"]["offset"]) for ( $rowCounter = 0; $rowCounter < SEARCH_ROWS && $rowCounter + $_SESSION["searchFormVars"]["offset"] < $result->numRows( ) && $row = $result->fetchRow(DB_FETCHMODE_ASSOC, $_SESSION["searchFormVars"]["offset"] + $rowCounter); $rowCounter++) { $template->setCurrentBlock("row"); $template->setVariable("YEAR", $row["year"]); $template->setVariable("WINERY", $row["winery_name"]); $template->setVariable("WINE", $row["wine_name"]); $template->setVariable("VARIETIES", showVarieties($connection, $row["wine_id"])); $price = showPricing($connection, $row["wine_id"]); $template->setVariable("BOTTLE_PRICE", sprintf("$%4.2f", $price)); $template->setVariable("DOZEN_PRICE", sprintf("$%4.2f", ($price*12))); $template->setVariable("ONEHREF", S_ADDTOCART . "?qty=1&wineId={$row["wine_id"]}"); $template->setVariable("DOZENHREF", S_ADDTOCART . "?qty=12&wineId={$row["wine_id"]}"); $template->parseCurrentBlock("row"); } // end for rows in the page // Show the row numbers that are being viewed $template->setCurrentBlock( ); $template->setVariable("BEGINROW", $_SESSION["searchFormVars"]["offset"] + 1); $template->setVariable("ENDROW", $rowCounter + $_SESSION["searchFormVars"]["offset"]); $template->setVariable("ROWS", $result->numRows( )); // Are there any previous pages? if ($_SESSION["searchFormVars"]["offset"] >= SEARCH_ROWS) { // Yes, so create a previous link $template->setCurrentBlock("link"); $template->setVariable("HREF", S_SEARCH . "?offset=" . rawurlencode($previousOffset) . "&{$browseString}"); $template->setVariable("HREFTEXT", "Previous"); $template->parseCurrentBlock("link"); } else { // No, there is no previous page so don't // print a link $template->setCurrentBlock("outtext"); $template->setVariable("OUTTEXT", "Previous"); $template->parseCurrentBlock("outtext"); } $template->setCurrentBlock("links"); $template->parseCurrentBlock("links"); // Output the page numbers as links // Count through the number of pages in the results for($x=0, $page=1; $x<$result->numRows( ); $x+=SEARCH_ROWS, $page++) { // Is this the current page? if ($x < $_SESSION["searchFormVars"]["offset"] || $x > ($_SESSION["searchFormVars"]["offset"] + SEARCH_ROWS - 1)) { // No, so print a link to that page $template->setCurrentBlock("link"); $template->setVariable("HREF", S_SEARCH . "?offset=" . rawurlencode($x) . "&{$browseString}"); $template->setVariable("HREFTEXT", $page); $template->parseCurrentBlock("link"); } else { // Yes, so don't print a link $template->setCurrentBlock("outtext"); $template->setVariable("OUTTEXT", $page); $template->parseCurrentBlock("outtext"); } $template->setCurrentBlock("links"); $template->parseCurrentBlock("links"); } // Are there any Next pages? if (isset($row) && ($result->numRows( ) > $nextOffset)) { // Yes, so create a next link $template->setCurrentBlock("link"); $template->setVariable("HREF", S_SEARCH . "?offset=" . rawurlencode($nextOffset) . "&{$browseString}"); $template->setVariable("HREFTEXT", "Next"); $template->parseCurrentBlock("link"); } else { // No, there is no next page so don't // print a link $template->setCurrentBlock("outtext"); $template->setVariable("OUTTEXT", "Next"); $template->parseCurrentBlock("outtext"); } $template->setCurrentBlock("links"); $template->parseCurrentBlock("links"); } // end if numRows( ) else { $template->setCurrentBlock("outtext"); $template->setVariable("OUTTEXT", "No wines found matching your criteria."); $template->parseCurrentBlock("outtext"); $template->setCurrentBlock("links"); $template->parseCurrentBlock("links"); } } // --------- session_start( ); $template = new winestoreTemplate(T_SEARCH); $connection = DB::connect($dsn, true); if (DB::isError($connection)) trigger_error($connection->getMessage( ), E_USER_ERROR); // Store the search parameters so the <form> redisplays the // previous search $_SESSION["searchFormVars"]["region_name"] = pearclean($_GET, "region_name", 100, $connection); $_SESSION["searchFormVars"]["wine_type"] = pearclean($_GET, "wine_type", 32, $connection); // If an offset isn't provided, set it to 0 if (isset($_GET["offset"])) $_SESSION["searchFormVars"]["offset"] = pearclean($_GET, "offset", 5, $connection); else $_SESSION["searchFormVars"]["offset"] = 0; // Show the user their search showWines($connection, $template); $template->showWinestore(SHOW_ALL, B_HOME | B_SHOW_CART | B_SEARCH | B_LOGINLOGOUT); ?> 20.2.2.1 Finding the winesThe query that retrieves the matching wines is implemented in the setupQuery( ) function in Example 20-2. The query performs a natural join between the wine, winery, and inventory tables, and displays the winery_name, year, wine_name, and wine attributes from those tables: SELECT DISTINCT wi.winery_name, w.year, w.wine_name, w.wine FROM wine w, winery wi, inventory i, region r, wine_type wt WHERE w.winery_id = wi.winery_id AND w.wine_id = i.wine_id The query includes the inventory table in the FROM clause because the user can only purchase wines that are in stock. The region and wine_type tables are included because they are the source of the user-supplied search criteria. The inventory table can have more than one row for each wine. Because it's included in the join, a wine can be returned from the query more than once, in the case where it's available at two or more prices or it arrived at the warehouse on two or more days. However, because we only want to see the details of each wine once in the browse screen, the query uses the DISTINCT clause to remove any duplicates. Depending on whether the user has supplied a wine type or a region as a search criteria, additional clauses are added to the query. For example, if the user supplies the region name Margaret River, the following is added: AND r.region_name = 'Margaret River' AND r.region_id = wi.region_id This restricts the answer set to only those wines that are from the Margaret River region, and includes the region table in the natural join. If the user supplies a wine type of Red, a similar clause is added for the wine_type table: AND wt.wine_type = 'Red' AND wt.wine_type_id = w.wine_type The additional clauses are omitted if the user selects All regions or All wine types. After adding the additional clauses as required, the last step in forming the query is to add sorting criteria. We sort the wines by winery_name, then by wine_name, and last by vintage year: ORDER BY wi.winery_name, w.wine_name, w.year 20.2.2.2 Displaying the winesThe results of the query are shown in pages of twelve wines each. Previous and Next page links are shown so that the user can move between pages, as well as page numbers shown as links that allow direct access to any page in the results. This is a useful technique to display large result sets in pages and it works as follows:
The main body of the script stores the search criteria region_name and wine_type in the session array searchFormVars. They're saved so that when the user revisits the search/searchform.php script, their previously entered search criteria are redisplayed. In addition, if an offset is supplied, it's saved. The offset is used to indicate which row should be the first row displayed on the page, and this is used to display pages when the user clicks on Next, Previous, or a page number. When the user runs their first search, the offset isn't supplied and it's set to zero. The showWines( ) function displays the search results. To do this, it uses the template templates/search.tpl shown in Example 20-3. The template has several blocks and placeholders that are used as follows:
Example 20-3. The templates/search.tpl template that's used to display search results<h1>{SEARCHCRITERIA}</h1> <table border="0"> <!-- BEGIN row --> <tr> <td>{YEAR} {WINERY} {WINE} {VARIETIES} <br><b>Our price: </b>{BOTTLE_PRICE} ({DOZEN_PRICE} a dozen) </td> <td><a href="{ONEHREF}">Add a bottle to the cart</a> </td> <td><a href="{DOZENHREF}">Add a dozen</a> </td> </tr> <!-- END row --> </table> <br>{BEGINROW} - {ENDROW} of {ROWS} wines found matching your criteria <br> <!-- BEGIN links --> <!-- BEGIN link --> <a href="{HREF}">{HREFTEXT}</a> <!-- END link --> <!-- BEGIN outtext --> {OUTTEXT} <!-- END outtext --> <!-- END links --> The showWines( ) function itself carries out the following steps:
|
Previous | Table of Contents | Next |