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

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

I have a codeigniter version for you. It also strips the quotes from the values.

function get_enum_values( $table, $field )
{
    $type = $this->db->query( "SHOW COLUMNS FROM {$table} WHERE Field = '{$field}'" )->row( 0 )->Type;
    preg_match("/^enum\(\'(.*)\'\)$/", $type, $matches);
    $enum = explode("','", $matches[1]);
    return $enum;
}

You can get the values by querying it 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

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 convert '' into just '.

The following will return $trimmedvals array items without quotes:

$trimmedvals = array();
foreach($vals as $key => $value) {
$value=trim($value, "'");
$trimmedvals[] = $value;
}

This is like a lot of the above, but gives you the result without loops, AND gets you want you really want: a simple array for generating select options.

BONUS: It works for SET as well as ENUM field types.

$result = $db->query("SHOW COLUMNS FROM table LIKE 'column'");
if ($result) {
    $option_array = explode("','",preg_replace("/(enum|set)\('(.+?)'\)/","\\2", $result[0]->Type));
}

$option_array: Array ( [0] => red [1] => green [2] => blue )

You can parse the string as though it was a CSV (Comma Separated Value) string. PHP has a great build-in function called str_getcsv which converts a CSV string to an array.

// This is an example to test with
$enum_or_set = "'blond','brunette','redhead'";

// Here is the parser
$options = str_getcsv($enum_or_set, ',', "'");

// Output the value
print_r($options);

This should give you something similar to the following:

Array
(
    [0] => blond
    [1] => brunette
    [2] => redhead
)

This method also allows you to have single quotes in your strings (notice the use of two single quotes):

$enum_or_set = "'blond','brunette','red''head'";

Array
(
    [0] => blond
    [1] => brunette
    [2] => red'head
)

For more information on the str_getcsv function, check the PHP manual: http://uk.php.net/manual/en/function.str-getcsv.php

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

Related

Recent Posts