CODEWITHSUNDEEP
phpmysql
Shamoon
Shamoon

Reputation: 43531

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?

Upvotes: 117

Views: 152978

Answers (21)

bradkt
bradkt

Reputation: 121

You can view, and edit, enums using DBeaver. Once you connect to the db view the table columns and enum columns will display the available values by default.

Upvotes: 0

jugi
jugi

Reputation: 702

This SQL statement will give you the possible enum values as rows, which might be easier to work with, in my case I used the statement with pandas and there it helped a lot to have it as rows.

SELECT POSSIBLE_VALUES 
FROM information_schema.COLUMNS C 
JOIN JSON_TABLE(
    CONCAT(
        '[', 
        REPLACE(
            SUBSTRING(C.COLUMN_TYPE, 6, CHAR_LENGTH(C.COLUMN_TYPE) - 6), 
            '\'', 
            '"'
        ), 
    ']'), 
    "$[*]" COLUMNS(POSSIBLE_VALUES VARCHAR(255) PATH "$")) j
WHERE C.TABLE_SCHEMA='<your_schema>' 
AND C.TABLE_NAME='<your_table_name>'
AND C.COLUMN_NAME='<your_column_name'
AND C.DATA_TYPE='enum'

Upvotes: 0

Patrick Savalle
Patrick Savalle

Reputation: 4426

This version also strips the quotes from the values.

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

Upvotes: 114

Lukas Eder
Lukas Eder

Reputation: 220932

Here's a pure SQL solution that covers most edge cases of encoding enums by parsing them recursively:

with recursive 
  e as (
    select
      table_schema, table_name, column_name,
      regexp_replace(column_type, 'enum\\((.*)\\)', '$1') as e
    from information_schema.columns
    where data_type like 'enum%'
  ),
  l as (
    select 
      table_schema, table_name, column_name,
      e, cast('' as char(32767)) as l,
      0 as p
    from e
    union all
    select
      table_schema,
      table_name,
      column_name,
      regexp_replace(e, '''.*?''(?:,|$)(.*)', '$1', 1, 1) as e,
      replace(
        regexp_replace(e, '''(.*?)''(?:,|$).*', '$1', 1, 1), 
        '''''', ''''
      ) as l,
      p + 1
    from l
    where length(e) > 0
  )
select table_schema, table_name, column_name, l, p
from l
where p > 0
order by table_schema, table_name, column_name, p;

When run against this table:

create table e (e enum ('e', '''', '\\', 'y'));

You'll get something like:

|TABLE_SCHEMA|TABLE_NAME|COLUMN_NAME|l  |p  |
|------------|----------|-----------|---|---|
|test        |e         |e          |e  |1  |
|test        |e         |e          |'  |2  |
|test        |e         |e          |\\ |3  |
|test        |e         |e          |y  |4  |

One edge case it doesn't handle is when it contains both ' and ,

Upvotes: 0

gerryino
gerryino

Reputation: 378

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) ☠"
}

Upvotes: 1

Dakusan
Dakusan

Reputation: 6691

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.

Upvotes: 4

Nahidul Hasan
Nahidul Hasan

Reputation: 45

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

Upvotes: 1

Marco Concas
Marco Concas

Reputation: 1892

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

Upvotes: -1

Troublemaker-DV
Troublemaker-DV

Reputation: 174

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()

Upvotes: 0

AhmadKarim
AhmadKarim

Reputation: 905

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;

}

Upvotes: -1

Arman
Arman

Reputation: 21

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');

Upvotes: 2

cchana
cchana

Reputation: 5000

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.

Upvotes: 4

user6577205
user6577205

Reputation:

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

Upvotes: 2

marco
marco

Reputation: 39

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)

Upvotes: 1

OraYa
OraYa

Reputation: 39

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()'"));
}

Upvotes: 3

Matthew
Matthew

Reputation: 48284

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

Upvotes: 61

jasonbar
jasonbar

Reputation: 13461

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

Upvotes: 38

Phreditor
Phreditor

Reputation: 477

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 )

Upvotes: 13

Scott Krelo
Scott Krelo

Reputation: 81

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!

Upvotes: 7

eggyal
eggyal

Reputation: 125865

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.

Upvotes: 10

bashaus
bashaus

Reputation: 1673

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

Upvotes: 11

Related Questions