Querying with User Input (Web Database Applications with PHP & MySQL)

The script uses the five-step process described in Chapter 4 to provide the following functionality:

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.

5.2.1. Combined Scripts

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.

Example 5-6. A combined <form> and processing script to display 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

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

Figure 5-6. Output of the combined script from Example 5-6

5.2.2. Adding Links to Results

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.

Example 5-7. The code used to add the "Add to Cart" link

echo "<tr align=\"right\"><td>" .
     "<a href=\"example.5-8.php?qty=1&amp;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.

WARNING: Be careful what information is embedded in links. For example, never embed the price of an item you later rely on to create an invoice for the user. Remember that the user can manually enter URLs in their browser and can modify any of the parameters. If a price is embedded, a user can create the URL manually and change the price of the item!

5.2.3. One-Component Querying

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.

Figure 5-7. The principle of one-component querying

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.

Example 5-8. Implementing one-component querying for the Add to Cart functionality

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

NOTE: The header( ) command can be issued only before data is sent. In one-component querying, the script that carries out the database actions shouldn't produce any output, so this usually isn't a problem. A call to the header( ) function should also be followed by an exit statement if no further processing of statements after the header( ) function call is desired. We discussed the symptoms of header( ) function problems and how to solve them in Chapter 2.

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.