Querying with User Input : PHP

Example 5-5. A script to display all wineries in a region

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