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

User · Answer

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

User · Answer

I have a codeigniter version for you  It also strips the quotes from the values   function get enum values   table   field          type    this- gt db- gt query   SHOW COLUMNS FROM   table  WHERE Field      field     - gt row  0  - gt Type      preg match    enum                  type   matches        enum   explode         matches 1        return  enum

User · Answer

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

User · Answer

For Laravel this worked    result   DB  select  SHOW COLUMNS FROM  table name  LIKE  status       regex                preg match all   regex    result 0 - gt Type   enum array     enum fields    enum array 1   echo   lt pre gt    print r  enum fields     Output   Array    0    gt  Requested  1    gt  Call Back  2    gt  Busy  3    gt  Not Reachable  4    gt  Not Responding

User · Answer

Codeigniter adapting version as method of some model   public function enum values  table name   field name         query    this- gt db- gt query  SHOW COLUMNS FROM    table name   LIKE    field name           if   query- gt num rows    return array        preg match all                    query- gt row  Type     matches        return  matches 1       Result   array 2         0   gt  string 13   administrator       1   gt  string 8   customer

User · Answer

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

User · Answer

try this  describe table columnname   gives you all the information about that column in that table

User · Answer

For PHP 5 6     mysqli   new mysqli  example com   username   password   database     result    mysqli- gt query  SELECT COLUMN TYPE FROM INFORMATION SCHEMA COLUMNS WHERE TABLE NAME  table name  AND COLUMN NAME  column name      row    result- gt fetch assoc    var dump  row

User · Answer

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    gt  blond      1    gt  brunette      2    gt  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    gt  blond      1    gt  brunette      2    gt  red head     For more information on the str getcsv function  check the PHP manual  http   uk php net manual en function str-getcsv php

User · Answer

This is one of Chris Komlenic s 8 Reasons Why MySQL s ENUM Data Type Is Evil       nbsp 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

User · Answer

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

User · Answer

All of you use some strange and complex regex patterns x   Here s my solution without preg match    function getEnumTypes  table   field         query    this- gt db- gt prepare  SHOW COLUMNS FROM  table WHERE Field            try   query- gt execute array  field     catch  Exception  e   error log  e- gt getMessage           types    query- gt fetchAll PDO  FETCH COLUMN PDO  FETCH UNIQUE  1   field       return explode        trim  types   enum

User · Answer

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

User · Answer

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

User · Answer

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- gt query  sql        row    result- gt 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

User · Answer

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    lt  php class Genre extends  SplEnum    const male    Male    const female    Female       http   it2 php net manual en class splenum php

User · Answer

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

User · Answer

here is for mysqli   function get enum values  mysqli   table   field          type    mysqli- gt query  SHOW COLUMNS FROM   table  WHERE Field      field    - gt 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

User · Answer

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

User · Answer

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    lt  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      gt

User · Answer

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    quot   wpdb- gt prefix   table  quot        query    quot SHOW COLUMNS FROM   table  WHERE Field      field   quot        results    wpdb- gt get results  query  ARRAY A        if  is array  results   amp  amp  count  results   gt  0             preg match  quot   enum               quot    results 0   Type     matches            if  is array  matches   amp  amp  isset  matches 1                    values   explode  quot     quot    matches 1                           return  values

User · Answer

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- gt query  SHOW COLUMNS FROM table LIKE  column     if   result         option array   explode       preg replace    enum set                  2    result 0 - gt Type         option array  Array        0     red      1     green      2     blue

User · Answer

row   db fetch object  result        if  row         type    row- gt Type       preg match all                 type   matches PREG PATTERN ORDER         return  matches 1

User · Answer

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 - gt Type   matches      foreach  explode       matches 1   as  value                enum     trim   value                     return  enum

User · Answer

Adding to cchana s answer  The method  quot length-6 quot  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   quot enum                                     quot   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   quot enum  in use   for removal   trashed   quot   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  quot simple quot  LENGTH

User · Answer

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

[php] How can I get enum possible values in a MySQL database?

Examples related to php

Examples related to mysql