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

Answer 3

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

Answer 4

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 )

Answer 5

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

Answer 6

This is one of Chris Komlenic's 8 Reasons Why MySQL's ENUM Data Type Is Evil:

 4. Getting a list of distinct ENUM members is a pain.

A very common need is to populate a select-box or drop down list with possible values from the database. Like this:

Select color:

[ select box ]

If these values are stored in a reference table named 'colors', all you need is: SELECT * FROM colors ...which can then be parsed out to dynamically generate the drop down list. You can add or change the colors in the reference table, and your sexy order forms will automatically be updated. Awesome.

Now consider the evil ENUM: how do you extract the member list? You could query the ENUM column in your table for DISTINCT values but that will only return values that are actually used and present in the table, not necessarily all possible values. You can query INFORMATION_SCHEMA and parse them out of the query result with a scripting language, but that's unnecessarily complicated. In fact, I don't know of any elegant, purely SQL way to extract the member list of an ENUM column.

Answer 7

A more up to date way of doing it, this worked for me:

function enum_to_array($table, $field) {    
    $query = "SHOW FIELDS FROM `{$table}` LIKE '{$field}'";
    $result = $db->query($sql);
    $row = $result->fetchRow();
    preg_match('#^enum\((.*?)\)$#ism', $row['Type'], $matches);
    $enum = str_getcsv($matches[1], ",", "'");
    return $enum;
}

Ultimately, the enum values when separated from "enum()" is just a CSV string, so parse it as such!

Answer 8

here is for mysqli

function get_enum_values($mysqli, $table, $field )
{
    $type = $mysqli->query("SHOW COLUMNS FROM {$table} WHERE Field = '{$field}'")->fetch_array(MYSQLI_ASSOC)['Type'];
    preg_match("/^enum\(\'(.*)\'\)$/", $type, $matches);
    $enum = explode("','", $matches[1]);
    return $enum;
}
$deltypevals = get_enum_values($mysqli, 'orders', 'deltype');
var_dump ($deltypevals);

Answer 9

Here is the same function given by Patrick Savalle adapted for the framework Laravel

function get_enum_values($table, $field)
{

   $test=DB::select(DB::raw("show columns from {$table} where field = '{$field}'"));

   preg_match('/^enum\((.*)\)$/', $test[0]->Type, $matches);
   foreach( explode(',', $matches[1]) as $value )
   {
       $enum[] = trim( $value, "'" );   
   }

   return $enum;

}

Answer 10

To fetch the list of possible values has been well documented, but expanding on another answer that returned the values in parenthesis, I wanted to strip them out leaving me with a comma separated list that would then allow me to use an explode type function whenever I needed to get an array.

SELECT
    SUBSTRING(COLUMN_TYPE, 6, LENGTH(COLUMN_TYPE) - 6) AS val
FROM
    information_schema.COLUMNS
WHERE
    TABLE_NAME = 'articles'
AND
    COLUMN_NAME = 'status'

The SUBSTRING now starts at the 6th character and uses a length which is 6 characters shorter than the total, removing the trailing parenthesis.

Answer 11

I simply want to add to what jasonbar says, when querying like:

SHOW columns FROM table

If you get the result out as an array it will look like this:

array([0],[Field],[1],[Type],[2],[Null],[3],[Key],[4],[Default],[5],[Extra])

Where [n] and [text] give the same value.
Not really told in any documentation I have found. Simply good to know what else is there.

Answer 12

All of you use some strange and complex regex patterns x)

Here's my solution without preg_match :

function getEnumTypes($table, $field) {
    $query = $this->db->prepare("SHOW COLUMNS FROM $table WHERE Field = ?");
    try {$query->execute(array($field));} catch (Exception $e) {error_log($e->getMessage());}
    $types = $query->fetchAll(PDO::FETCH_COLUMN|PDO::FETCH_UNIQUE, 1)[$field];
    return explode("','", trim($types, "enum()'"));
}

Answer 13

For Laravel this worked:

$result = DB::select("SHOW COLUMNS FROM `table_name` LIKE 'status';");
$regex = "/'(.*?)'/";
preg_match_all( $regex , $result[0]->Type, $enum_array );
$enum_fields = $enum_array[1];
echo "<pre>";
print_r($enum_fields);

Output:

Array
(
[0] => Requested
[1] => Call Back
[2] => Busy
[3] => Not Reachable
[4] => Not Responding
)

Answer 14

The problem with every other answer in this thread is that none of them properly parse all special cases of the strings within the enum.

The biggest special case character that was throwing me for a loop was single quotes, as they are encoded themselves as 2 single quotes together! So, for example, an enum with the value 'a' is encoded as enum('''a'''). Horrible, right?

Well, the solution is to use MySQL to parse the data for you!

Since everyone else is using PHP in this thread, that is what I will use. Following is the full code. I will explain it after. The parameter $FullEnumString will hold the entire enum string, extracted from whatever method you want to use from all the other answers. RunQuery() and FetchRow() (non associative) are stand ins for your favorite DB access methods.

function GetDataFromEnum($FullEnumString)
{
    if(!preg_match('/^enum\((.*)\)$/iD', $FullEnumString, $Matches))
        return null;
    return FetchRow(RunQuery('SELECT '.$Matches[1]));
}

preg_match('/^enum\((.*)\)$/iD', $FullEnumString, $Matches) confirms that the enum value matches what we expect, which is to say, "enum(".$STUFF.")" (with nothing before or after). If the preg_match fails, NULL is returned.

This preg_match also stores the list of strings, escaped in weird SQL syntax, in $Matches[1]. So next, we want to be able to get the real data out of that. So you just run "SELECT ".$Matches[1], and you have a full list of the strings in your first record!

So just pull out that record with a FetchRow(RunQuery(...)) and you’re done.

If you wanted to do this entire thing in SQL, you could use the following

SET @TableName='your_table_name', @ColName='your_col_name', @DBName='your_database_name';
SET @Q=(SELECT CONCAT('SELECT ', (SELECT SUBSTR(COLUMN_TYPE, 6, LENGTH(COLUMN_TYPE)-6) FROM information_schema.COLUMNS WHERE TABLE_NAME=@TableName AND COLUMN_NAME=@ColName AND TABLE_SCHEMA=@DBName)));
PREPARE stmt FROM @Q;
EXECUTE stmt;

P.S. To preempt anyone from saying something about it, no, I do not believe this method can lead to SQL injection.

Answer 15

try this

describe table columnname

gives you all the information about that column in that table;

Answer 16

Codeigniter adapting version as method of some model:

public function enum_values($table_name, $field_name)
{
    $query = $this->db->query("SHOW COLUMNS FROM `{$table_name}` LIKE '{$field_name}'");

    if(!$query->num_rows()) return array();
    preg_match_all('~\'([^\']*)\'~', $query->row('Type'), $matches);

    return $matches[1];
}

Result:

array(2) {
    [0]=> string(13) "administrator"
    [1]=> string(8) "customer"
}

Answer 17

this will work for me:

SELECT REPLACE(SUBSTRING(COLUMN_TYPE,6,(LENGTH(COLUMN_TYPE)-6)),"'","")
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA='__TABLE_SCHEMA__' 
AND TABLE_NAME='__TABLE_NAME__'
AND COLUMN_NAME='__COLUMN_NAME__'

and then

explode(',', $data)

Answer 18

You can use this syntax for get enum possible values in MySQL QUERY :

$syntax = "SELECT COLUMN_TYPY FROM information_schema.`COLUMNS` 
WHERE TABLE_NAME = '{$THE_TABLE_NAME}' 
AND COLUMN_NAME = '{$THE_COLUMN_OF_TABLE}'";

and you get value, example : enum('Male','Female')

this is example sytax php:

<?php
function ($table,$colm){

// mysql query.
$syntax = mysql_query("SELECT COLUMN_TYPY FROM information_schema.`COLUMNS` 
WHERE TABLE_NAME = '$table' AND COLUMN_NAME ='$colm'");

if (!mysql_error()){
 //Get a array possible values from table and colm.
 $array_string = mysql_fetch_array($syntax);

    //Remove part string
    $string = str_replace("'", "", $array_string['COLUMN_TYPE']);
    $string = str_replace(')', "", $string);
    $string = explode(",",substr(5,$string));
}else{
    $string = "error mysql :".mysql_error();
}
// Values is (Examples) Male,Female,Other
return $string;
}
?>

Answer 19

I get enum values in this way:

SELECT COLUMN_TYPE 
FROM information_schema.`COLUMNS` 
WHERE TABLE_NAME = 'tableName' 
     AND COLUMN_NAME = 'columnName';

Running this sql I have get : enum('BDBL','AB Bank')

then I have filtered just value using following code :

preg_match("/^enum\(\'(.*)\'\)$/", $type, $matches);
$enum = explode("','", $matches[1]);
var_dump($enum) ;

Out put :

array(2) {
  [0]=>
  string(4) "BDBL"
  [1]=>
  string(7) "AB Bank"
}

Answer 20

DELIMITER //

    DROP FUNCTION IF EXISTS ENUM_VALUES;

    CREATE FUNCTION ENUM_VALUES(

        _table_name VARCHAR(64), 
        _col_name VARCHAR(64)

    ) RETURNS JSON

        BEGIN

            RETURN (
                SELECT CAST(CONCAT('[', REPLACE(SUBSTRING(COLUMN_TYPE, 6, LENGTH(COLUMN_TYPE) - 6), "'", '"'), ']') AS JSON)
                  FROM information_schema.COLUMNS
                 WHERE TABLE_SCHEMA = 'db_name'
                   AND TABLE_NAME   = _table_name
                   AND COLUMN_NAME  = _col_name
                   AND DATA_TYPE    = 'enum'
            );

        END //

DELIMITER ;

Example:

SELECT ENUM_VALUES('table_name', 'col_name');

Answer 21

just for fun, i wrote a FSM solution which is UTF-8 safe and quote-resistant.

I assumed that mysql/mariadb always uses single quotes to wrap values and doubles the quote for a literal ' .

$string=$column_info;

$char = '';
$next_char = '';
$buffer = '';
$buffering = false;
$enum_values = array();

while( mb_strlen($string) > 0 ){
  // consume a multibyte char
  $char=mb_substr($string,0,1);
  $string=mb_substr($string,1);

  if ( $char === "'" && $buffering === false ) {
    // start buffering
    $buffering=true;
  } else if ( $char === "'" && $buffering === true){
    // see next char
    $next_char=mb_substr($string,0,1);
    $string=mb_substr($string,1);

    if( $next_char === "'" ){
      // two quote '' found, literal
      $buffer = "$buffer$char";
    } else {
      // end of string
      $enum_values[] = $buffer;
      $buffer = '';
      $buffering = false;
    }

  } else if( $buffering === true ) {
    // normal char during buffering
    $buffer = "$buffer$char";
  }
}

if( $buffering ){
  // this means an unterminated string
  throw new \Exception("Unterminated string in enum");

}

return $enum_values;

tested against:

array(7) {
  [0]=>
  string(10) "added enum"
  [1]=>
  string(22) "a "double Quoted" enum"
  [2]=>
  string(6) "normal"
  [3]=>
  string(26) "an utf-8 enum ☠ (middle)"
  [4]=>
  string(15) "a 'Quoted' enum"
  [5]=>
  string(21) "a single quote ' enum"
  [6]=>
  string(23) "an utf-8 enum (end) ☠"
}

Answer 22

$row = db_fetch_object($result);
     if($row){
     $type = $row->Type;
     preg_match_all("/'([^']+)'/", $type, $matches,PREG_PATTERN_ORDER );
     return $matches[1];


}

Answer 23

For PHP 5.6+

$mysqli = new mysqli("example.com","username","password","database");
$result = $mysqli->query("SELECT COLUMN_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='table_name' AND COLUMN_NAME='column_name'");
$row = $result->fetch_assoc();
var_dump($row);

Answer 24

For Yii framework there is little change with:

function getEnumValues($table, $field)
{
    $stmt = " SHOW COLUMNS FROM {{{$table}}} WHERE Field = '$field'";
    $type = Yii::app()->db->createCommand($stmt)->queryRow()["Type"];
    preg_match("/^enum\(\'(.*)\'\)$/", $type, $matches);
    return explode("','", $matches[1]);
}

Answer 25

It is extraordinary how none of you has thought that if you are using an enum field it means that the values to be assigned are known "a priori".

Therefore if the values are known "a priori" the best ways to manage them is through a very simple Enum class.

Kiss rule and save one database call.

<?php
class Genre extends \SplEnum {
 const male = "Male";
 const female = "Female";
}

http://it2.php.net/manual/en/class.splenum.php

Answer 26

SELECT
    SUBSTRING(COLUMN_TYPE, 6, LENGTH(COLUMN_TYPE) - 6) AS val
FROM
    information_schema.COLUMNS
WHERE
    TABLE_NAME = 'articles'
AND
    COLUMN_NAME = 'status'

Wouldn't work for enum('','X''XX')

Answer 27

Here is a solution for a custom WordPress table. This will work for ENUM values without a comma (,) in them

function get_enum_values($wpdb, $table, $field) {

    $values = array();
    $table = "{$wpdb->prefix}{$table}";
    $query = "SHOW COLUMNS FROM {$table} WHERE Field = '{$field}'";
    $results = $wpdb->get_results($query, ARRAY_A);

    if (is_array($results) && count($results) > 0) {

        preg_match("/^enum\(\'(.*)\'\)$/", $results[0]['Type'], $matches);

        if (is_array($matches) && isset($matches[1])) {

            $values = explode("','", $matches[1]);

        }

    }

    return $values;

}

Answer 28

Adding to cchana's answer. The method "length-6" fails on non-latin values in enum.

For example (the values are in Cyrillic, table is UTF8 - utf8_general_ci. In the examples I use the variable for simplicity: selecting from schema gives the same):

set @a:="enum('в работе','на списание','списано')";
select substring(@a,6,length(@a)-6);
+-------------------------------------------------------------+
| substring(@a,6,length(@a)-6)                                |
+-------------------------------------------------------------+
| 'в работе','на списание','списано')                         |
+-------------------------------------------------------------+

Note the closing parenthesis?

select right(@a,1);
+-------------+
| right(@a,1) |
+-------------+
| )           |
+-------------+

Well, let's try remove one more character:

select substring(@a,6,length(@a)-7);
+-------------------------------------------------------------+
| substring(@a,6,length(@a)-7)                                |
+-------------------------------------------------------------+
| 'в работе','на списание','списано')                         |
+-------------------------------------------------------------+

No luck! The parenthesis stays in place.

Checking (mid() function works in way similar to substring(), and both shows the same results):

select mid(@a,6,length(@a)/2);
+---------------------------------------------------------+
| mid(@a,6,length(@a)/2)                                  |
+---------------------------------------------------------+
| 'в работе','на списание','списан                        |
+---------------------------------------------------------+

See: the string lost only three rightmost characters. But should we replace Cyrillic with Latin, and all works just perfectly:

set @b:="enum('in use','for removal','trashed')";
select (substring(@b,6,length(@b)-6));
+----------------------------------+
| (substring(@b,6,length(@b)-6))   |
+----------------------------------+
| 'in use','for removal','trashed' |
+----------------------------------+

JFYI

Edit 20210221: the solution for non-Latin characters is CHAR_LENGTH() instead of "simple" LENGTH()

Answer 29

This will work using PDO:

$stmt = $mysql->prepare("SHOW COLUMNS FROM table LIKE 'column'");
$stmt->execute();
$enumvalues = $stmt->fetch(PDO::FETCH_ASSOC)['Type'];
$enumvalues = explode(',', str_replace('\'', '', substr($enumvalues, 5, strlen($enumvalues) - 6)));