Getting The Values Of A Mysql Enum Using Only Sql
Solution 1:
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 2:
While I would agree about not using enums most of the time, it is possible in a single SQL statement:-
SELECTDISTINCT SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING(COLUMN_TYPE, 7, LENGTH(COLUMN_TYPE) -8), "','", 1+ units.i + tens.i *10) , "','", -1)
FROM INFORMATION_SCHEMA.COLUMNS
CROSSJOIN (SELECT0AS i UNIONSELECT1UNIONSELECT2UNIONSELECT3UNIONSELECT4UNIONSELECT5UNIONSELECT6UNIONSELECT7UNIONSELECT8UNIONSELECT9) units
CROSSJOIN (SELECT0AS i UNIONSELECT1UNIONSELECT2UNIONSELECT3UNIONSELECT4UNIONSELECT5UNIONSELECT6UNIONSELECT7UNIONSELECT8UNIONSELECT9) tens
WHERE TABLE_NAME ='mytable'AND COLUMN_NAME ='mycolumn'This will work for enums with up to 100 possible values
Solution 3:
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'"
) ordie("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($newArrayas$value){
echo$value . "\n<br>";
}
Post a Comment for "Getting The Values Of A Mysql Enum Using Only Sql"