MySQL Cookbook

Applying Table Structure Information

Problem

It’s all well and good to be able to obtain table structure information, but what can you use it for?

Solution

Lots of things: displaying lists of table columns, creating web form elements, producing ALTER TABLE statements for modifying ENUM or SET columns, and more.

Discussion

This section describes some applications for the table structure information that MySQL provides.

Displaying Column Lists

Probably the simplest use of table information is to present a list of the table’s columns. This is common in web-based or GUI applications that allow users to construct queries interactively by selecting a table column from a list and entering a value against which to compare column values. The various versions of the get_column_names_with_show( ) or get_column_names_with_meta( ) functions shown earlier in the chapter can serve as the basis for such list displays.

Interactive Record Editing

Knowledge of a table’s structure can be very useful for interactive record-editing applications. Suppose you have an application that retrieves a record from the database, displays a form containing the record’s content so a user can edit it, and then updates the record in the database after the user modifies the form and submits it. You can use table structure information for validating column values. For example, if a column is an ENUM, you can find out the valid enumeration values and check the value submitted by the user against them to determine whether ...