To
conclude this chapter, we present a short case study of dynamically
producing <form>
components from a database.
The techniques used are an application of the five-step querying
process from Chapter 4.
We have already identified that the scripts in most of this
chapter’s examples require that the user remember
and reproduce the names of the wine regions. A far better
approach—and one that works well for small numbers of
items—is to present values using the HTML
<select>
input type. For the wine regions,
the <select>
input has the following
structure:
<select name="regionName"> <option selected> All <option> Barossa Valley <option> Coonawarra <option> Goulburn Valley <option> Lower Hunter Valley <option> Margaret River <option> Riverland <option> Rutherglen <option> Swan Valley <option> Upper Hunter Valley </select>
With only a small number of wine regions, it is tempting to develop a
static HTML page with an embedded list of region names. However, this
is poor and inflexible. If the region database
table changes—that is, new regions are added or deleted or you
want to change a region_name
value—you have
to remember to update the HTML page. Moreover, a spelling mistake or
an extra space when creating the HTML page renders a
<select>
option useless, because it no
longer matches the values in the database when used for querying. A
better approach is to use the techniques from Chapter 4 to dynamically query the database and produce
a <select>
element using the
region_name
values stored in the
region table.
Consider the approach of dynamically producing HTML. First, you
retrieve the set of different values of the
region_name
attribute in the
region table. Then, you format the values as
HTML <option>
elements and present a HTML
<form>
to the user. When the user chooses a
region and submits the <form>
, you should
run a query that uses the region name the user selected as one of the
query parameters to match against data in the database and to produce
a result set. Because the values chosen by the user in the
<form>
are compared against database values,
it makes sense that the list values should originate from the
database.
In this section, we develop a component that can be reused to produce
select
lists in different modules of a web
database application. An example that uses this new component is
shown in Example 5-12.
Example 5-12. Producing an HTML <form> that contains a database-driven select list
// Connect to the DBMS if (!($connection = @ mysql_connect($hostName, $username, $password))) showerror( ); if (!mysql_select_db($databaseName, $connection)) showerror( ); echo " Region: "; // Produce the select list // Parameters: // 1: Database connection // 2. Table that contains values // 3. Attribute that contains values // 4. <SELECT> element name // 5. An additional non-database value // 6. Optional <OPTION SELECTED> selectDistinct($connection, "region", "region_name", "regionName", "All", "All"); echo " <br><input type="submit"" . "value="Show wines">" . " </form> <br>"; echo "<a href="index.html">Home</a>";
The component itself is discussed later but is encapsulated in the
function selectDistinct( )
, which takes the following parameters:
A database connection handle, in this case, a connection opened with
mysql_connect
and stored in
$connection
.
A database name, $database
, which is a variable
that is set to winestore
in the include file
db.inc,
as discussed in Chapter 4.
The database table from which to produce the list. In this case, the table region contains the region name data.
The database table attribute with the values to be used as the text
for each <option>
element shown to the user
in the list. In this example, it’s
region_name
from the region
table.
The name of the HTML <select>
element. We
use regionName
, but this can be anything and
isn’t dependent on the underlying database.
An additional option to add to the list if required; the value
All
doesn’t occur in the
region database table but is an extra value
added to the list.
An optional default value to output as the <option selected>
in the list; this option is shown as selected
when the user accesses the page. All
is used as a
default here.
The output of the function for the parameters used in Example 5-12 is shown in Figure 5-9.
The remainder of the script fragment in Example 5-12 produces the other required tags in the HTML document.
This section details the implementation of the
generic
selectDistinct( )
function. The function
produces a <select>
list with an optional
<option selected>
element using attribute values
retrieved from a database table. One additional non-database item can
be added to the list. The body of the function is shown in Example 5-13.
Example 5-13. The body of the selectDistinct( ) function for producing select lists
function selectDistinct ($connection, $tableName, $columnName, $pulldownName, $additionalOption, $defaultValue) { $defaultWithinResultSet = FALSE; // Query to find distinct values of $columnName // in $tableName $distinctQuery = "SELECT DISTINCT $columnName FROM $tableName"; // Run the distinctQuery on the databaseName if (!($resultId = @ mysql_query ($distinctQuery, $connection))) showerror( ); // Retrieve all distinct values $i = 0; while ($row = @ mysql_fetch_array($resultId)) $resultBuffer[$i++] = $row[$columnName]; // Start the select widget echo " <select name="$pulldownName">"; // Is there an additional option? if (isset($additionalOption)) // Yes, but is it the default option? if ($defaultValue == $additionalOption) // Show the additional option as selected echo " <option selected>$additionalOption"; else // Just show the additional option echo " <option>$additionalOption"; // check for a default value if (isset($defaultValue)) { // Yes, there's a default value specified // Check if the defaultValue is in the // database values foreach ($resultBuffer as $result) if ($result == $defaultValue) // Yes, show as selected echo " <option selected>$result"; else // No, just show as an option echo " <option>$result"; } // end if defaultValue else { // No defaultValue // Show database values as options foreach ($resultBuffer as $result) echo " <option>$result"; } echo " </select>"; } // end of function
The implementation of selectDistinct( )
is
useful for most cases in which a <select>
list needs to be produced. The first section of the code queries the
table $tableName
passed as a parameter, extracts
the values of the attribute $columnName
—also
passed as a parameter—into an array
$resultBuffer
, and produces a
<select>
element with the name attribute
$pulldownName
. The code is a five-step querying
module.
The remainder of the code deals with the possible cases for a default
value passed though as $defaultValue
:
If there is an $additionalOption
, it is output as
an <option>
. If it is also the default
option, it is output as the <option selected>
.
If there is no $defaultValue
passed through as a
parameter, the code produces an option for each value in
$resultBuffer
with no <option selected>
.
If there is a $defaultValue
, the code iterates
through the $resultBuffer
to see if this value is
in the result set. If the value does occur in the
$resultBuffer
, it is output as the
<option selected>
.
The regionName
select
list for
the online winestore has the default option of
All
—which isn’t a region in
the region table—and this is added
manually to the list of options the user can choose from.
Generic, database-independent—or at least
table-independent—code is a useful addition to a web database
application. Similar functions to selectDistinct( )
can be developed using the same five-step process to
produce radio buttons, checkboxes, multiple-select lists, or even
generic complete <form>
pages
based
on a database table.
18.225.235.144