CODEWITHSUNDEEP
phpmysqlsqlenums
gumgl
gumgl

Reputation: 55

Getting the values of a MySQL enum using only SQL

In a web application, I need to populate a <select> with all possible values of a MySQL enum.

When I execute either of: 

SHOW COLUMNS FROM  mytable LIKE  'mycolumn';
SELECT COLUMN_TYPE FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = 'mytable' AND COLUMN_NAME = 'mycolumn';
I always end up with enum('valueA','valueB','valueC').

I know I can parse it with PHP but can it be done using SQL only? I need something like this:

+-----------------+
| values          |
+-----------------+
| valueA          |
| valueB          |
| valueC          |
+-----------------+

Upvotes: 5

Views: 10079

Answers (3)

thewordsmith
thewordsmith

Reputation: 1

This is the simplest solution

$EnumColum = $mysqli->query(
  $link,
  "SELECT COLUMN_TYPE FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = 'mrb_shipping_carriers' AND COLUMN_NAME = 'uspsServiceType'"
) or die("Error in " . ": " . __FILE__ . ": " . __LINE__);

$replace1 = preg_replace("/enum/", '',$EnumColum[0]['COLUMN_TYPE']);
$replace2 = preg_replace("/\(/", '',$replace1);
$replace3 = preg_replace("/\)/", '',$replace2);
$replace4 = preg_replace("/\'/", '',$replace3);
$newArray = explode(',',$replace4);
foreach($newArray as $value){
    echo $value . "\n<br>";
}

Upvotes: 0

Kickstart
Kickstart

Reputation: 21513

While I would agree about not using enums most of the time, it is possible in a single SQL statement:-

SELECT DISTINCT SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING(COLUMN_TYPE, 7, LENGTH(COLUMN_TYPE) - 8), "','", 1 + units.i + tens.i * 10) , "','", -1)
FROM INFORMATION_SCHEMA.COLUMNS
CROSS JOIN (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) units
CROSS JOIN (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) tens
WHERE TABLE_NAME = 'mytable' 
AND COLUMN_NAME = 'mycolumn'

This will work for enums with up to 100 possible values

Upvotes: 4

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

Related Questions