Gomiworld

Although using MySQL enums is often discouraged I sometimes use them for their simplicity. Today however I stumbled upon one of the problems with enums. There is no simple method to determine all possible values of an enum column.

While it was easy enough to extract with a little creative PHP the solution is rather ugly. I'd much prefer to be able to write "SELECT options(enum_column) FROM table_name" which would then result in one row per option.

Until we come to our senses and stop using enums in MySQL I guess we are stuck with hacks like the following:

// First get the result of $query into variable $column using your prefered db abstraction.
// In my environment I use $db->fetch()
// You can user mysqli or pdo or whatever. Ex:
// $result = mysql_query(, $con);
// $column = mysql_fetch_row($result)

$query = "SHOW columns FROM table WHERE Field = 'enum_column'";
$column = $db->fetch($query);

// $column['Type'] will contain the definition for the enum and it will look something like:
// enum('value1', 'value2', 'value3')
// What we want to do is convert this in to an array. We could do this by replacing "enum" with array
// and then run the string through eval(). But since eval is a "dangerous" function we'll do it using json.
// So first, we do a little string replacing so that we end up with a string looking like:
// ["value1", "value2", "value3"]
//  We then pass that string to json_decode so that we get an array.
$options = json_decode(str_replace(
    array("enum(", ")", "'"),
    array("[", "]", '"'),
    $column['Type']
));

// Now that we have all the options in an array it's simple to test if a certain option is present.
if (in_array("foobar", $options))
{
    doSomething();
}

That's all folks!