20.2 Searching and Browsing

This 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 Form

Example 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 Results

Example 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"]) .

                   "&amp;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&amp;wineId={$row["wine_id"]}");

         $template->setVariable("DOZENHREF", S_ADDTOCART .

                                "?qty=12&amp;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) .

                                "&amp;{$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) .

              "&amp;{$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) .

             "&amp;{$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 wines

The 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 wines

The 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:

  • When the user inputs their search criteria for the first time, only the first 12 rows (with indexes 0 to 11) of matching wines are shown.

  • An embedded Next link is shown 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, with indexes of 12 to 23.

  • When the user reaches the last page of results (which usually has less than 12 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.

  • Page numbers are displayed that allow direct access to other pages without repeatedly clicking on the previous or next links. The current page isn't shown as a link.

  • For each wine on the page, an embedded link is shown that allows the user to add one or a dozen bottles of the wine to the shopping cart using the cart/addtocart.php script discussed in Chapter 18.

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:

  • The SEARCHCRITERIA placeholder displays the parameters that have been used in the search process. For example, if the user is browsing Margaret River Red wines, it is set to <h1>Region: Margaret River Wine type: Red</h1>.

  • The row block displays each of the wines that match the search criteria. The wine details are shown in the YEAR, WINERY, WINE, and VARIETIES placeholders, the prices in BOTTLE_PRICE and DOZEN_PRICE, and the links that are used to add wines to the shopping cart in ONEHREF and DOZENHREF.

  • At the base of the list of wines, the placeholders BEGINROW, ENDROW, and ROWS show information about the range of wines that are displayed on the pages. For example, on the first page of results, this may show 1 - 12 of 38 wines found matching your criteria.

  • The links block is output once for the Previous and Next text, and once for each page number that's needed at the base of a results page. It contains the link and outtext blocks, and each time a links block is output, only one of the nested link or outtext blocks are shown.

  • The link block is used for a hypertext link that points at HREF and is labeled with HREFTEXT. For example, the link text could be set to Next and the hypertext link to /wda2-winestore/search/search.php?offset=12&amp;wine_type=Red&amp;region_name=Margaret+River. Alternatively, the link text might be set to a page number or Previous.

  • The outtext block is used to display text that isn't a hypertext link. This is used when there's no previous or next page, or to show the page number of the current page.

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:

  1. It outputs the heading using the SEARCHCRITERIA placeholder.

  2. It creates a $browseString that includes the current search parameters. This is used as part of any embedded links at the base of the results page. For example, if the user wants Margaret River Red wines, the $browseString is:

    wine_type=Red&amp;region_name=Margaret+River

    The urlencode( ) function is used to convert strings to text suitable for a URL by, for example, converting spaces to plus signs. You'll also notice that we've converted the & to an &amp; entity reference as required by the HTML recommendation.

  3. The setupQuery( ) function discussed previously is used to formulate the query, and the query is executed.

  4. If any results are returned, they're displayed as discussed next. If no wines are found that match the criteria, a message is output using the OUTTEXT placeholder that states No wines found matching your criteria.

  5. The relative offsets of Previous and Next links are calculated. For example, if the current page begins at row 0, the $nextOffset is set to 12 and the $previousOffset to -12. If the $previousOffset is less than 0, a previous link isn't shown. If the $nextOffset is greater than the number of rows in the result set, a next link isn't shown.

  6. The for loop outputs the rows on the page. The for loop continues to retrieve rows and increment $rowCounter while three conditions hold: first, the $rowCounter is less than 12; second, the offset plus the $rowCounter is less than the number of rows in the result set; and, last, fetching the row that is the sum of the $rowCounter and the offset succeeds. The final two conditions are only important on the last page of results when there are less than 12 results to display. To read a specific row in the result set, the optional second parameter to the PEAR DB::fetchRow( ) method is used; this is discussed in Chapter 7.

  7. For each row that's output, the helper functions showVarieties( ) and showPricing( ) are used to find the grape varieties and the cheapest price of the wine. These are part of the winestore.inc include file that's discussed in Chapter 16.

  8. After the rows are output, the BEGINROW, ENDROW, and ROWS placeholders are populated as discussed previously.

  9. To conclude the function, the script produces the Previous, Next, and page number links. The previous link is created with the following code fragment:

    // 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) .
    
           "&amp;{$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");

    A Previous link is produced only if the first row displayed on the page is greater than SEARCH_ROWS (which is set to 12); this is true if we've just produced the second or a later page. The link itself points to the search/search.php script with the offset variable set to the value of $previousOffset calculated earlier, and the parameter $browseString provides the region name and wine type criteria for the next search.

    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.

  10. The page number links are output using a similar approach to the previous and next links. A for loop counts through the rows in the result set, one page of 12 wines at a time. When the counter is set to a row number on the page we've just displayed, a textual page number is produced. If the counter isn't on the current page, the page number is output as a link that has its offset set to the first row on that page. As previously, the $browseString stores the search criteria.