How can I get enum possible values in a MySQL database?
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;
}
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;
}
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 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