Introduction

The previous chapter describes how to retrieve information from MySQL and display it in web pages using various types of HTML constructs such as tables or hyperlinks. That's a use of MySQL to send information in one direction (from web server to user), but web-based database programming can also serve to collect information sent in the other direction from user to web server, such as the contents of a submitted form. If you're processing a survey form, you might store the information for later use. If the form contains search keywords, you'd use them as the basis for a query that searches the database for information the user wants to see.

MySQL comes into these activities in a fairly obvious way, as the repository for storing information or as the source from which search results are drawn. But before you can process input from a form, you have to create the form and send it to the user. MySQL can help with this, too, because it's often possible to use information stored in your database to generate form elements such as radio buttons, checkboxes, pop-up menus, or scrolling lists:

By using database content to generate form elements, you lessen the amount of table-specific knowledge your programs must have and allow them to determine what they need automatically. A script that uses a database to figure out for itself how to generate form elements adaptively handles changes to the database. To add a new country, create a new row in the table that stores the list of countries. To add a new salutation, change the definition of the ENUM column. In each case, you change the set of items in a form element by updating the database, not by modifying the script; the script adapts to the change automatically, without additional programming.

The first part of this chapter covers the following topics relating to web input processing:

The second part of the chapter illustrates a few ways to apply the techniques developed in the first part. These include applications that show how to use MySQL to present a web-based search interface, create paged displays that contain next-page and previous-page links, implement per-page hit counting and logging, and perform general Apache logging to a database.

For the Perl, PHP, and Python example scripts discussed in this chapter, look under the apache directory of the recipes distribution. For JSP, the scripts are under the tomcat directory; you should already have installed these in the process of setting up the mcb application context (Recipe 16.4). Library routines used by the example scripts are located in files found under the lib directory. Scripts to create the tables used here are located in the tables directory.

Note that although the scripts in this chapter are intended to be invoked from your browser after they have been installed, many of them (JSP pages excepted) can also be invoked from the command line if you wish to see the raw HTML they produce.

To provide a concrete context for discussion, many of the form-processing examples in this chapter are based on the following scenario: in the lucrative field of "construct-a-cow" business endeavors, you run an operation that manufactures build-to-order ceramic bovine figurines, and you want to design an online ordering application that lets customers make selections for several aspects of the product. For each order, it's necessary to collect several types of information:

Given the preceding discussion, a cow_order table might be designed like this:

CREATE TABLE cow_order ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, # cow color, figurine size, and accessory items color CHAR(20), size ENUM('small','medium','large') DEFAULT 'medium', accessories SET('cow bell','horns','nose ring','tail ribbon') DEFAULT 'cow bell,horns', # customer name, street, city, and state (abbreviation) cust_name CHAR(40), cust_street CHAR(40), cust_city CHAR(40), cust_state CHAR(2), PRIMARY KEY (id) );

The id column provides a unique identifier for each record. It's a good idea to have such a value, and in fact will be necessary when we get to Recipe 18.5, which shows how to use web forms to edit existing records. For that type of activity, you must be able to tell which record to update, which is difficult without a unique record identifier.

The list of available colors is maintained in a separate table, cow_color:

CREATE TABLE cow_color ( color CHAR(20) );

For purposes of illustration, assume that the color table looks like this:

+---------------+ | color | +---------------+ | Black | | Black & White | | Brown | | Cream | | Red | | Red & White | | See-Through | +---------------+

An application can use these tables to generate list elements in an order entry form, making it unnecessary for the application to have a lot of specialized built-in knowledge about the available options. The next several sections describe how to do this, and how to process the input that you obtain when a user submits a form.

Категории