Code, Docs & Tools

MySQL: get values from an enum/set field definition

MySQL enum/set fiels

enum and set fields in MySQL allow to define a list of values the field can contain. The difference between the two field types is that enum allows to be filled with only one (or zero) of the allowed values, whilst set can be filled with more than one value.

When changing the list of allowed values, two changes could be required: since they are stored in the MySQL table definition, if the list of values is also hardcoded in the PHP code it's required to update both the field definition and the application code.

To avoid this, the list of allowed values could be loaded dinamically in the PHP application, reading the table definition:

function loadEnumSetValues($table, $column) {
  $values = $GLOBALS["DBL"]->query("SHOW COLUMNS FROM " . $table . " LIKE '" . $field . "'")->fetch_object();
  $values = explode("','", preg_replace("/(enum|set)\('(.+?)'\)/", "\2", $values->Type));
  return $values;
}

In the above example, you have to replace the $DBL variable with the one that contains the MySQL link identifier.

For example, to obtain the list of values defined for the field enumField in the table testTable, you can call the function in this way:

$values = loadEnumSetValues("testTable", "enumField");

Please be aware that $table and $field should be properly escaped if this code is used in a non-secure environment, to avoid risk of query-injection attacks.