How can I get enum possible values in a MySQL database?
PhpMysqlPhp Problem Overview
I want to populate my dropdowns with enum possible values from a DB automatically. Is this possible in MySQL?
Php Solutions
Solution 1 - Php
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;
}
Solution 2 - Php
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
Solution 3 - Php
> 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;
}
Solution 4 - Php
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 )
Solution 5 - Php
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
Solution 6 - Php
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.
Solution 7 - Php
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!
Solution 8 - Php
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);
Solution 9 - Php
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;
}
Solution 10 - Php
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.
Solution 11 - Php
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.
Solution 12 - Php
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()'"));
}
Solution 13 - Php
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
)
Solution 14 - Php
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.
Solution 15 - Php
$row = db_fetch_object($result);
if($row){
$type = $row->Type;
preg_match_all("/'([^']+)'/", $type, $matches,PREG_PATTERN_ORDER );
return $matches[1];
}
Solution 16 - Php
try this
describe table columnname
gives you all the information about that column in that table;
Solution 17 - Php
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"
}
Solution 18 - Php
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)
Solution 19 - Php
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;
}
?>
Solution 20 - Php
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"
}
Solution 21 - Php
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) ☠"
}
Solution 22 - Php
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);
Solution 23 - Php
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');
Solution 24 - Php
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";
}
Solution 25 - Php
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')
Solution 26 - Php
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;
}
Solution 27 - Php
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()
Solution 28 - Php
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)));