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

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; } 

Answer 2

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