How can I get enum possible values in a MySQL database?

Question

I want to populate my dropdowns with enum possible values from a DB automatically. Is this possible in MySQL?

Answer 1

MySQL Reference

If you want to determine all possible values for an ENUM column, use SHOW COLUMNS FROM tbl_name LIKE enum_col and parse the ENUM definition in the Type column of the output.

You would want something like:

$sql = "SHOW COLUMNS FROM `table` LIKE 'column'";
$result = $db->query($sql);
$row = $result->fetchRow();
$type = $row['Type'];
preg_match('/enum\((.*)\)$/', $type, $matches);
$vals = explode(',', $matches[1]);

This will give you the quoted values. MySQL always returns these enclosed in single quotes. A single quote in the value is escaped by a single quote. You can probably safely call trim($val, "'") on each of the array elements. You'll want to covnert '' into just '.

Answer 2

I simply want to add to what jasonbar says, when querying like:

SHOW columns FROM table

If you get the result out as an array it will look like this:

array([0],[Field],[1],[Type],[2],[Null],[3],[Key],[4],[Default],[5],[Extra])

Where [n] and [text] give the same value.
Not really told in any documentation I have found. Simply good to know what else is there.

Answer 3

You can get the values like this:

SELECT SUBSTRING(COLUMN_TYPE,5)
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA='databasename' 
    AND TABLE_NAME='tablename'
    AND COLUMN_NAME='columnname'"

From there you'll need to convert it into an array:

• eval that directly into an array if you're lazy (although MySQL's single quote escape might be incompatible), or
• $options_array = str_getcsv($options, ',', "'") possibly would work (if you alter the substring to skip the opening and closing parentheses), or
• a regular expression