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?

1
90
2/28/2010 4:09:44 AM

Accepted Answer

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;
}
91
10/17/2014 7:39:30 PM

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

Licensed under: CC-BY-SA with attribution
Not affiliated with: Stack Overflow
Icon