Web App Log

Example 6-1 does have one undesirable side effect that is common in web database applications. The problem isn't really related to modifying the database but rather to the statelessness of the HTTP protocol. We discuss this side effect—the reload problem—and an effective solution in the next section.

  // Is there any data?
 if ($rowsFound != 0)
 {
// Yes, there is data.

// (2a) The "Previous" page begins at the current
// offset LESS the number of ROWS per page
$previousOffset = $rowOffset - ROWS;

// (2b) The "Next" page begins at the current offset
// PLUS the number of ROWS per page
$nextOffset = $rowOffset + ROWS;

// (3) Seek to the current offset
if (!mysql_data_seek($result, $rowOffset))
	showerror();

// (4a) Output the header and start a table
echo $pageHeader;
echo "<table border=\"1\">\n<tr>";

// (4b) Print out the column headers from $header
foreach ($header as $element)
	echo "\n\t<th>" . $element["header"] . "</th>";

echo "\n</tr>";

// (5a) Fetch one page of results (or less if on the
// last page)
for ( $rowCounter = 0;
	 (($rowCounter < ROWS) &&
	  ($row = @ mysql_fetch_array($result)) );
	 $rowCounter++)
{
	// Print out a row
	echo "\n<tr>";

	// (5b) For each of the attributes in a row
	foreach($header as $element)
	{
		echo "\n\t<td>";

		// Get the database attribute name for the
		// current attribute
		$temp = $element["attrib"];

		// Print out the value of the current
		// attribute
		echo $row["$temp"];

		echo "</td>";
	} // end foreach attribute

	echo "\n</tr>\n";
} // end for rows in the page

// Finish the results table, and start a footer
echo "\n</table>\n<br>";

Key Terms:

Example 5-11:

• mysql_data_seek() - Line 37 of WebAppFuncs::browse in file ./inc/class.inc

Page Browsing Tools:

• Display Results over several pages
• move between several page results
• Code as Reusable Module

Purpose of 5.3:

• How to handle LARGE result-sets
• efficiency
• scroll-bar-avoidance
• intuitive goal
• direct-access: all results
• nav the results with Prev and Next

Result Sets:

• How to display over several pages
• Prev and Next Links
• Add Page Number Functionality

Ex. 5-8 will be the final result of this section

one-component querying

the query input component is displayed, but there is no corresponding page that shows output of the query.

The key to one-component querying is to have an HTTP header("Location: ...") sent as a response to the web browser. 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.

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 Chapter 6. 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: $_SERVER['HTTP_REFERER']");
      exit;
    } else
      echo "Incorrectly called.";
?>  

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.

The function displayWinesList() is introduced for querying the database.

displayWinesList()

1. produces a <table> with headings
2. processes the result set
3. produces <table> rows
4. finishes the </table> with a message indicating how many records are present in the table

[ How to pre-process ] user data to solve many common security holes in web database applications.

For this purpose, we have authored the clean( ) function to ensure that the data passed to a script is of the correct length and that special characters aren't misused to attack the system. To understand why the clean( ) function is needed, we describe an example attack later in this section. The function is part the include file db.inc that is used in all scripts in the online winestore.

Three techniques [to pass data] in a web database application:

1. Manual entry of a URL to retrieve a PHP script resource and provide parameters to the resource. For example, a user may open a URL using the Open Page option in the File menu of the Netscape web browser.
2. Data entry through HTML
   environments. For example, environments can capture textual input, and input is made by selecting radio buttons, selecting one or more items from a drop-down select list, clicking on buttons, and through other data entry widgets.
3. Embedded hypertext links that can be clicked to retrieve a PHP script resource and provide parameters to the script.

4.3 Engineering a Front Panel

The panel component developed in this chapter is the basis of the front page of our online winestore. However, shopping cart features that are not discussed in detail here have been added to the production version shown in Figure 4-1. The finalized code that includes the shopping-cart functionality is discussed further in Chapter 5, and the completed code is presented in Chapter 11.

In engineering the panel, we use the following techniques:

• Querying with the MySQL proprietary LIMIT modifier

• Using SQL table aliases in querying

• Using the HTML <table> environment as a presentation tool

• Producing consolidated HTML output from multiple SQL queries

• Presenting data based on calculations

• Using MySQL functions ”especially mysql_fetch_array() ”in practice

4.2 Formatting Results

The script in Example 4-6 uses the function displayWines() to present the results as an HTML <table>.

The displayWines( ) function first outputs a <table> tag, followed by a table row <tr> tag with six <th> header tags and descriptions matching the six attributes of the wine table. We could have output these using mysql_fetch_field( ) to return the attribute names [...] However [...] attribute names are less meaningful to users than manually constructed textual descriptions.

Chapter 3 - MySQL & SQL

Chapter 3 is an excellent! chapter to learn about the following topics:

• A short introduction to relational databases and relational modeling

• A quick start guide to the winestore database and its full entity-relationship model

• The MySQL command interpreter and the basic features of MySQL

• Using SQL to create and modify databases, tables, and indexes

• Using SQL to insert, delete, and update data

• The SQL SELECT statement for querying, with examples of simple and advanced queries

• Functions and operators in SQL and MySQL

• Advanced features, including managing indexes and keys, tuning the MySQL DBMS, security, and the limitations of MySQL

In addition to the familiar mysql_error(), there is also available its compliment, mysql_errno, which is unique as in the following description:

int mysql_errno(resource   connection)  

Returns the error number of the last error on the connection resource

NOTE:
The MySQL error-handling functions should be used with the @ operator that suppresses default output of error messages by the PHP script engine. Omitting the @ operator produces messages that contain both the custom error message and the default error message produced by PHP.

Several MySQL functions shouldn't be used in practice:

The functions of mysql_fetch_field() are also available in the non-object-based alternatives mysql_fetch_length(), mysql_field_flags(), mysql_field_name(), mysql_field_len(), mysql_field_table(), and mysql_field_type(); as these functions are almost a complete subset of mysql_fetch_field(), we don't describe them here.

The function mysql_result() is a slower alternative to fetching and processing a row with mysql_fetch_row() or mysql_fetch_array() and shouldn't be used in practice.

mysql_fetch_assoc() fetches a row of results as an associative array only, providing half the functionality of mysql_fetch_array(). The other half fetching into an array accessed by numeric index is provided by mysql_fetch_row(). Since mysql_fetch_array() provides both sets of functionality or can provide the same functionality by passing through MYSQL_ASSOC as the second parameter it should be used instead.

mysql_field_seek() can seek to a specific field for a subsequent call to mysql_fetch_field(), but this is redundant because the field number can be supplied directly to mysql_fetch_field() as the optional second parameter.

mysql_db_query() combines the functionality of mysql_select_db() and mysql_query(). This function has been deprecated in recent releases of PHP.