MySQL Cookbook, 2nd Edition

Using Table Structure Information in Applications

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 are possible: you can display lists of table columns, create web form elements, produce ALTER TABLE statements for modifying ENUM or SET columns, and more.

Discussion

This section describes some uses for the table structure information that MySQL makes available.

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 statements interactively by selecting a table column from a list and entering a value against which to compare column values. The get_column_names() routines shown in Accessing Table Column Definitions 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 that 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 it’s legal. If the column ...