Section 19.0. Introduction

19.0. 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 is also useful for collecting information sent in the other direction (from user to web server), such as the contents of a submitted form. For example, 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:

• You can select a set of items from a table that lists countries, states, or provinces and convert them into a pop-up menu in a form that collects address information.

• You can use the list of legal values for an ENUM column that contains allowable salutations (Mr., Mrs., and so forth) to generate a set of radio buttons.

• You can use lists of available colors, sizes, or styles stored in an inventory database to construct fields for a clothing ordering form.

• If you have an application that enables the user to pick a database or table, you can query the MySQL server for a list of databases or tables and use the resulting names to create a list element.

By using database content to generate form elements, you lessen the amount of explicit knowledge your programs must have about table structure and content, and you enable them to determine what they need automatically. A script that uses database content to figure out for itself how to generate form elements will also adaptively handle 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:

Generating forms and form elements

One way to use database content for form construction is by selecting a list of items from a table and using them to create the options in a list element. But metadata can be used as well. There is a natural correspondence between ENUM columns and single-pick form elements like radio button sets or pop-up menus. In both cases, only one from a set of possible values may be chosen. There is a similar correspondence between SET columns and multiple-pick elements like checkbox groups; any or all of the possible values may be chosen. To construct metadata-based form elements, obtain the column description from the table metadata stored in INFORMATION_SCHEMA, extract the set of legal ENUM or SET values, and use them for the items in the form element.

Initializing forms using database contents

In addition to using the database to create structural elements of forms, you can also use it to initialize forms. For example, to enable a user to modify an existing record, retrieve it from the database and load it into a form's fields before sending the form to the user for editing.

Processing input gathered over the Web

This includes input not only from form fields, but also the contents of uploaded files, or parameters that are present in URLs. Regardless of how you obtain the information, you'll face a common set of issues in dealing with it: extracting and decoding the information, performing constraint or validity checking on it, and re-encoding the information for statement construction to avoid generating malformed statements or storing information inaccurately.

The second part of the chapter illustrates several 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 Apache logging to a database.

Scripts to create the tables used in this chapter are located in the tables directory of the recipes distribution. The scripts for the examples can be found under the directories named for the servers used to run them. For Perl, Ruby, PHP, and Python examples, look under the apache directory. Utility routines used by the example scripts are found in files located in the lib directory. (See Section 17.2 for information on configuring Apache so that scripts can be run by it and find their library files.) For Java (JSP) examples, look under the tomcat directory; you should already have installed these examples in the process of setting up the mcb application context (Section 17.3).

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 want to see the raw HTML they produce. (See Section 17.2.)

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:

Cow color

The particular list of colors available at any particular time changes occasionally, so for flexibility, the values can be stored in a database table. To change the set of colors that customers can choose from, just update the table.

Cow size

There is a fixed set of sizes that doesn't change often (small, medium, large), so the values can be represented as elements of an ENUM column.

The all-important cow accessory items

These include a bell, horns, a sporty-looking tail ribbon, and a nose ring. Accessories can be represented in a SET column, because a customer may want to select more than one of them. In addition, you know from past experience that most customers order horns and a cow bell, so it's reasonable to use those for the column's default value.

Customer name and address (street, city, state)

The possible state names are already stored in the states table, so we can use them as the basis for the corresponding form element.

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

CREATE TABLE cow_order (   id          INT UNSIGNED NOT NULL AUTO_INCREMENT,   # cow color, figurine size, and accessory items   color       VARCHAR(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   VARCHAR(40),   cust_street VARCHAR(40),   cust_city   VARCHAR(40),   cust_state  CHAR(2),   PRIMARY KEY (id) ); 

The id column provides a unique identifier for each row. It's a good idea to have such a value, and in fact will be necessary when we get to Section 19.4, which shows how to use web forms to edit existing rows. For that type of activity, you must be able to tell which row to update, which is difficult to do without a unique row 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 cow_color table contains the following rows:

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

An application can use the tables just described 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 recipes describe how to do this, and how to process the input that you obtain when a user submits a form.