HomePHP Page 4 - Changing Table Structure in phpMyAdmin
ENUM and SET - PHP
This chapter explores editing table definitions and using special column types. When developing Web applications (or any application), requirements often change because of new or modified needs. Developers must accommodate these changes through judicious table-structure editing. This is chapter six of Mastering phpMyAdmin for Effective MySQL Management by Marc Delisle (Packt Publishing, April 2004, ISBN 1904811035).
Both these field types are intended to represent a list of possible values; the difference is that the user can choose only one value from a defined list of values with ENUM, and more than one value with SET. With SET, the multiple values all go into one cell; multiple values do not imply the creation of more than one row of data.
We add a field named genre and define it as an ENUM. For now, we choose to put short codes in the value list, and one of them (F) as the default value:
In the value list, we have to enclose each value within single quotes, unlike in the default value field. In our design, we know that these values stand for Fantasy, Child, and Novel, but for now we want to see the interface's behavior with short codes. In the Insert panel, we now see a radio box interface:
If we decide to have more self-describing codes, we can go back to Structure mode and change the definition for the genre field. In the following example, we do not see the complete value list because the field is not large enough, but what we entered was 'Fantasy', 'Child', 'Novel'. We also have to change the default value to one of the possible values, to avoid getting an error message while trying to saving this file structure modification.
With the modified value list, the Insert panel now looks as follows:
Observe that the previous radio buttons have changed to a select list because of the longer size of the possible values.
If we want more than one possible value selected, we have to change the field type to SET. The same value list may be used, but now, using our browser's multiple value selector (usually control-click) we can select more that one value:
For the previous example, we would store only the genre codes in the books table in a normalized data structure, and would rely on another table to store the description for each code. In fact, we would not be using a SET or ENUM in this case.
This chapter is from Mastering phpMyAdmin for Effective MySQL Management by Marc Delisle (Packt Publishing, April 2004, ISBN 1904811035). Check it out at your favorite bookstore today.