| only for RuBoard - do not distribute or recompile | |
To introduce querying with user input, we begin by explaining a script that retrieves the wines made in a wine region that is specified by a user. This script, shown in Example 5-5, is a companion to the HTML <form> in Example 5-2.
<!DOCTYPE HTML PUBLIC
"-//W3C//DTD HTML 4.0 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<title>Exploring Wines in a Region</title>
</head>
<body bgcolor="white">
<?php
include 'db.inc';
// Show all wines in a region in a <table>
function displayWinesList($connection,
$query,
$regionName)
{
// Run the query on the DBMS
if (!($result = @ mysql_query ($query, $connection)))
showerror( );
// Find out how many rows are available
$rowsFound = @ mysql_num_rows($result);
// If the query has results ...
if ($rowsFound > 0)
{
// ... print out a header
echo "Wines of $regionName<br>";
// and start a <table>.
echo "\n<table>\n<tr>" .
"\n\t<th>Wine ID</th>" .
"\n\t<th>Wine Name</th>" .
"\n\t<th>Type</th>" .
"\n\t<th>Year</th>" .
"\n\t<th>Winery</th>" .
"\n\t<th>Description</th>\n</tr>";
// Fetch each of the query rows
while ($row = @ mysql_fetch_array($result))
{
// Print one row of results
echo "\n<tr>" .
"\n\t<td>" . $row["wine_id"] . "</td>" .
"\n\t<td>" . $row["wine_name"] . "</td>" .
"\n\t<td>" . $row["type"] . "</td>" .
"\n\t<td>" . $row["year"] . "</td>" .
"\n\t<td>" . $row["winery_name"] . "</td>" .
"\n\t<td>" . $row["description"] . "</td>" .
"\n</tr>";
} // end while loop body
// Finish the <table>
echo "\n</table>";
} // end if $rowsFound body
// Report how many rows were found
echo "$rowsFound records found matching your
criteria<br>";
} // end of function
// Secure the user parameter $regionName
$regionName = clean($regionName, 30);
// Connect to the MySQL DBMS
if (!($connection = @ mysql_connect($hostName,
$username,
$password)))
die("Could not connect");
if (!mysql_select_db($databaseName, $connection))
showerror( );
// Start a query ...
$query = "SELECT w.wine_id,
w.wine_name,
w.description,
w.type,
w.year,
wry.winery_name
FROM winery wry, region r, wine w
WHERE wry.region_id = r.region_id
AND w.winery_id = wry.winery_id";
// ... then, if the user has specified a region,
// add the regionName as an AND clause ...
if ($regionName != "All")
$query .= " AND r.region_name = \"$regionName\"";
// ... and then complete the query.
$query .= " ORDER BY w.wine_name";
// run the query and show the results
displayWinesList($connection, $query, $regionName);
// Close the DBMS connection
mysql_close($connection);
?>
</body>
</html>
The script in Example 5-5 uses the querying techniques discussed in Chapter 4. This example differs from the others in several ways:
It expects input of a wine region to be provided through the HTTP attribute regionName.
The automatically initialized variable $regionName is untainted with the clean( ) function we discussed in the last section.
The value of the variable $regionName is used in querying.
The script uses the five-step process described in Chapter 4 to provide the following functionality:
Connect to the MySQL DBMS. The variable $hostName is set in db.inc along with the username $username and password $password. The code then selects the database name set in db.inc.
Build an SQL query, $query, to find wine and winery information for the region entered by the user through the <form> in Example 5-1.
The variable $regionName is used to construct a query on the winestore database, making the query dependent on the user input and, therefore, a user-driven query. This works as follows: if the user enters a regionName into the <form>, an additional AND clause is added to the query that restricts the r.region_name to be equal to the user-supplied region name. For example, if the user enters Margaret River, the clause:
AND r.region_name = "Margaret River"
is added to the query.
If the $regionName is All, no restriction on region is made, and the query retrieves wines for all regions.
The function displayWinesList( ) is then called to run the query.
displayWinesList( ) produces a <table> with headings, processes the result set and produces <table> rows, and finishes the </table> with a message indicating how many records are present in the table. This is similar functionality to the scripts discussed in Chapter 4.
Other than the processing of the user parameter and the handling of the All regions option, no significant new functionality is introduced in allowing the user to drive the query process in this example. We improve the processing and develop more modular code in the next section.
The approach described in the last section separates the HTML <form> and the PHP processing script into two files. It is more common to implement both in the same script where the code can produce a <form> or run a query, depending if user parameters are supplied. If the script is called with no parameters, the script produces a <form> for user input and, if it is called with input from the <form>, it runs the query. This is called a combined script.
For wine searching, a combined script is implemented by replacing the main section of Example 5-5 with the code fragment shown in Example 5-6. The difference between the two scripts is that Example 5-6 has the structure:
// Has the user provided the parameter?
if (empty($regionName))
{
// Yes, produce the HTML <form> to collect a regionName
} else
{
// No, run the query for wines in the region $regionName
}
With this structure, when the variable $regionName is empty—that is, the user has not yet entered anything—the user <form> is produced. When a value has been entered, the query is run and the results are output. Example 5-6 shows you how to replace the main section of the code from Example 5-5 with the <form> from Example 5-2. With this modification, only one file is required to produce the user form and then process the query output.
<!DOCTYPE HTML PUBLIC
"-//W3C//DTD HTML 4.0 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<title>Exploring Wines in a Region</title>
</head>
<body bgcolor="white">
<?php
include 'db.inc';
// Show all wines in a region in a <table>
function displayWinesList($connection,
$query,
$regionName)
{
// Run the query on the DBMS
if (!($result = @ mysql_query ($query, $connection)))
showerror( );
// Find out how many rows are available
$rowsFound = @ mysql_num_rows($result);
// If the query has results ...
if ($rowsFound > 0)
{
// ... print out a header
echo "Wines of $regionName<br>";
// and start a <table>.
echo "\n<table>\n<tr>" .
"\n\t<th>Wine ID</th>" .
"\n\t<th>Wine Name</th>" .
"\n\t<th>Type</th>" .
"\n\t<th>Year</th>" .
"\n\t<th>Winery</th>" .
"\n\t<th>Description</th>\n</tr>";
// Fetch each of the query rows
while ($row = @ mysql_fetch_array($result))
{
// Print one row of results
echo "\n<tr>" .
"\n\t<td>" . $row["wine_id"] . "</td>" .
"\n\t<td>" . $row["wine_name"] . "</td>" .
"\n\t<td>" . $row["type"] . "</td>" .
"\n\t<td>" . $row["year"] . "</td>" .
"\n\t<td>" . $row["winery_name"] . "</td>" .
"\n\t<td>" . $row["description"] . "</td>" .
"\n</tr>";
} // end while loop body
// Finish the <table>
echo "\n</table>";
} // end if $rowsFound body
// Report how many rows were found
echo "$rowsFound records found matching your
criteria<br>";
} // end of function
$scriptName = "example.5-6.php";
// Has the user provided the parameter?
if (empty($regionName))
{
// No, the user hasn't provided a parameter
?>
<form action="<?=$scriptName;?>" method="GET">
<br>Enter a region to browse :
<input type="text" name="regionName" value="All">
(type All to see all regions)
<br>
<input type="submit" value="Show wines">
</form><br>
<a href="index.html">Home</a>
<?php
} // end of if empty($regionName) body
else
{
// Secure the user parameter $regionName
$regionName = clean($regionName, 30);
// Connect to the MySQL DBMS
if (!($connection = @ mysql_connect($hostName,
$username,
$password)))
die("Could not connect");
if (!mysql_select_db($databaseName, $connection))
showerror( );
// Start a query ...
$query = "SELECT w.wine_id,
w.wine_name,
w.description,
w.type,
w.year,
wry.winery_name
FROM winery wry, region r, wine w
WHERE wry.region_id = r.region_id
AND w.winery_id = wry.winery_id";
// ... then, if the user has specified a region,
// add the regionName as an AND clause ...
if ($regionName != "All")
$query .= " AND r.region_name = \"$regionName\"";
// ... and then complete the query.
$query .= " ORDER BY w.wine_name";
// run the query and show the results
displayWinesList($connection, $query, $regionName);
// Close the DBMS connection
mysql_close($connection);
} // end of else if empty($regionName) body
?>
</body>
</html>
We use this combined script structure throughout the rest of this book. Output of Example 5-6 with the Margaret River parameter is shown in Figure 5-6.
As discussed in the earlier section Section 5.1.3, scripts can also include embedded URLs with parameters that can run queries. This is a powerful tool, and one that is used in most web database applications. In this section, we show the power of this technique with an example from the winestore. In the next section, we show how embedded URLs can be used in a longer case study.
In Chapter 4, we authored the panel to display the latest wines that have been added to the winestore. We noted that the panel used in the winestore has Add to Cart functionality, in which a user can click on a link, and a bottle or case of wine is added to her shopping cart. This functionality is implemented using an embedded URL that is dynamically created from data in the database. Example 5-7 displays the code used to add the "Add to Cart" link that's embedded in the panel. The code creates a URL with parameters that specify the quantity and the product to add to the shopping cart.
echo "<tr align=\"right\"><td>" .
"<a href=\"example.5-8.php?qty=1&wineId=" .
$row["wine_id"] .
"\">Add a bottle to the shopping cart</a>" .
"</td></tr>";
The code fragment in Example 5-7 creates a link such as:
http://localhost/example.5-8.php?qty=1&wineId=801
The URL parameter wineId is formed with the database wine_id attribute value that is associated with the current wine being displayed in the panel. When the user clicks the link, example.5-8.php is requested and the parameters are supplied to the script. The user can type the URL directly her their web browser with the same effect, or you can author a <form> for the same purpose. We discuss the script example.5-8.php in the next section.
In many web database applications, functionality is included that allows the user to click on a link that performs an action but allows the user to remain on the same page. This is one-component querying, in which the query input component is displayed, but there is no corresponding page that shows output of the query. In this section, we discuss how one-component querying is used and the principles of adding one-component queries to an application.
Figure 5-7 illustrates the principle of one-component querying. When the user selects a link on a page—let's assume this page is named browse.php and we refer to this as the calling page—an HTTP request for a PHP script addcart.php is sent to the server. At the server, the script addcart.php is interpreted by the PHP script engine and, after carrying out the database actions in the script, no output is produced. Instead—and this is the key to one-component querying—an HTTP Location: header is sent as a response to the web browser, and this header causes the browser to request the original calling page, browse.php. The result is that the calling page is redisplayed, and the user has the impression that he remained on the query input component page.
A good example of an application of one-component queryING was illustrated in the last section, where we showed how Add to Cart functionality can be incorporated in the winestore panel. One excellent way to support Add to Cart is to author a script that adds the wine to the user's cart and then redirects the user back to the panel. The cart is updated after a click, and the user can continue reading about and, hopefully, purchasing wines.
Example 5-8 shows a one-component script. In practice, the script adds a quantity of a specific wine to a shopping cart, using the parameters embedded in the links in the page generated by the script in Example 5-7. However, for simplicity we have not included the database queries here; modifying the database is the subject of the next chapter, and the full code for this example is presented in Chapter 11.
<?
if (!empty($wineId) && !empty($qty))
{
// Database functionality goes here
// This is the key to one-component querying:
// Redirect the browser back to the calling page,
// using the HTTP response header "Location:"
// and the PHP environment variable $HTTP_REFERER
header("Location: $HTTP_REFERER");
exit;
} else
echo "Incorrectly called.";
?>
The key to Example 5-8 is the final two lines of a successful execution of the script:
header("Location: $HTTP_REFERER");
exit;
The header( ) function sends an additional HTTP response header. In one-component querying, the response includes the Location header that redirects a browser to another URL, in this case the URL of the calling page. The URL of the calling page is automatically initialized into the PHP web server environment variable $HTTP_REFERER. The exit statement causes the script to abort after sending the header.
Consider an example where the calling page is the resource example.5-7.php that is output by the script in Example 5-7. This is the page that shows the user the Hot New Wines panel and allows the user to click on a link to add an item to her shopping basket. The user then clicks on a link on this page and requests this URL:
http://localhost/example.5-8.php?qty=1&wineId=801
After successfully completing the request by running the script in Example 5-8 and adding the item to the shopping cart, the following header is sent back to the browser as a response:
Location: http://localhost/example.5-7.php
This header redirects the browser back to the calling page, completing the one-component query.
|
One-component querying is useful in situations where only the query screen is required or the results page and the query page are the same page. For example, in the winestore, one-component querying is used to update quantities in the shopping cart when the user alters the quantities of wine in his shopping cart. In general, one-component querying works well for simple update operations; these are the subject of Chapter 6.
| only for RuBoard - do not distribute or recompile | |
