MySQL Cookbook

Creating Multiple-Pick Form Elements from Database Content

Problem

A form needs to present a field that offers the user a set of options and allows any number of them to be selected.

Solution

Use a multiple-pick list element, such as a set of checkboxes or a scrolling list.

Discussion

Multiple-pick form elements allow you to present multiple choices, any number of which can be selected, or possibly even none of them. For our example scenario in which customers order cow figurines online, the multiple-pick element is represented by the set of accessory items that are available. The accessory column in the cow_order table is represented as a SET, so the allowable and default values can be obtained from the following query:

mysql> SHOW COLUMNS FROM cow_order LIKE 'accessories'\G
*************************** 1. row ***************************
  Field: accessories
   Type: set('cow bell','horns','nose ring','tail ribbon')
   Null: YES
    Key:
Default: cow bell,horns
  Extra:

This set of items can reasonably be represented as either a set of checkboxes or as a multiple-pick scrolling list. In both cases, the cow bell and horns items should be selected initially, because each is present in the column’s default value. I will discuss the HTML syntax for these elements, then show how to generate them from within scripts. (The material in this section relies heavily on Recipe 18.3, which discusses radio buttons, pop-up menus, and single-pick scrolling lists. I assume you’ve already read that section.)

• Checkboxes ...