MySQL Cookbook, 2nd Edition

Creating Multiple-Pick Form Elements from Database Content

Problem

A form needs to present a field that offers several options and enables the user to select any number of them.

Solution

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

Discussion

Multiple-pick form elements enable 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 with the following statement:

mysql>SELECT COLUMN_TYPE, COLUMN_DEFAULT FROM INFORMATION_SCHEMA.COLUMNS
    -> WHERE TABLE_SCHEMA='cookbook' AND TABLE_NAME='cow_order'
    -> AND COLUMN_NAME='accessories';
+---------------------------------------------------+----------------+
| COLUMN_TYPE                                       | COLUMN_DEFAULT |
+---------------------------------------------------+----------------+
| set('cow bell','horns','nose ring','tail ribbon') | cow bell,horns |
+---------------------------------------------------+----------------+

The values listed in the definition can reasonably be represented as either a set of checkboxes or as a multiple-pick scrolling list. Either way, the cow bell and horns items should be selected initially, because each is present in the column’s default value. The following discussion shows the ...