MySQL Cookbook, 2nd Edition

Creating Single-Pick Form Elements from Database Content

Problem

A form needs to present a field that offers several options but enables the user to select only one of them.

Solution

Use a single-pick list element. These include radio button sets, pop-up menus, and scrolling lists.

Discussion

Single-pick form elements enable you to present multiple choices from which a single option can be selected. Our construct-a-cow scenario involves several sets of single-pick choices:

• The list of colors in the cow_color table. These can be obtained with the following statement:

  mysql>SELECT color FROM cow_color ORDER BY color;
  +---------------+
  | color         |
  +---------------+
  | Black         |
  | Black & White |
  | Brown         |
  | Cream         |
  | Red           |
  | Red & White   |
  | See-Through   |
  +---------------+

  Note that some of the colors contain a & character, which is special in HTML. This means they will need HTML-encoding when placed into list elements. (We’ll perform encoding for all the list elements in the form as a matter of course, but those values illustrate why it’s a good idea to get in that habit.)

• The list of legal figurine sizes in the size column of the cow_order table. The column is represented as an ENUM, so the possible values and the default value can be obtained from INFORMATION_SCHEMA:

  mysql>SELECT COLUMN_TYPE, COLUMN_DEFAULT
      -> FROM INFORMATION_SCHEMA.COLUMNS
      -> WHERE TABLE_SCHEMA='cookbook' AND TABLE_NAME='cow_order'
      -> AND COLUMN_NAME='size'; +--------------------------------+----------------+ | COLUMN_TYPE | ...