Web Database Applications With Php And Mysql (2nd Edition) [Electronic resources] نسخه متنی

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=">Add a bottle to the cart</a>
</td>
<td><a href=">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=">{HREFTEXT}</a>
<!-- END link -->
<!-- BEGIN outtext -->
{OUTTEXT}
<!-- END outtext -->
<!-- END links -->

The showWines( ) function itself carries out the
following steps:

It outputs the heading using the SEARCHCRITERIA
placeholder.

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

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

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.

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.

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.

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.

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

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

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.