Sadly, the code snippets on MySQL’s main website for PHP are mostly untested and buggy (try running them – half of them don’t execute because of silly mistakes).
After much trial and error, here’s one that *actually works*:
// Missing feature (?) from MySQL: find the list of valid ENUM values for a given ENUM // (actually, returns all the value-arrays for ALL the enum fields in a given table, by name) // --------------------------------------------------------- function fetchEnumValuesForTable( $tablename ) { global $db; // assuming you're using PEAR:DB here, and throughout (I use it, or MDB, exclusively) $enumresult = $db->query("SHOW COLUMNS FROM $tablename"); // Makes arrays out of all ENUM type fields. // Uses the field names as array names and skips non-ENUM fields while( $enumrow = $enumresult->fetchRow() ) { extract($enumrow); if (substr($Type, 0, 4) != 'enum') continue; $Type = str_replace('enum', 'array', $Type); // Add to array eval( '$tmp = '."$Type;" ); // I'm not sure why, but I had to do this // intermediate step to get it to work $results[$Field] = $tmp; } return $results; // returns an array mapping each enum's "column name" // to "array of elements valid fo that ENUM" }
One reply on “PHP: how to fetch all possible values of an ENUM from MySQL”
Much better function:
function get_set_values($table, $field) {
if (preg_match(‘/`/’, $table) || preg_match(‘/\’/’, $field)) return false;
$column = mysql_fetch_array(mysql_query(“SHOW COLUMNS FROM `”.$table.”` LIKE ‘”.$field.”‘”));
if (!preg_match(‘/^enum|set/’, $column[‘Type’])) return false;
$vals = preg_replace(‘/(?:^enum|set)|\(|\)/’, ”, $column[‘Type’]);
$values = split(‘,’, $vals);
if (!sizeof($values)) return false;
for ($i = 0; $i < sizeof($values); $i++) {
$values[$i] = preg_replace(‘/^\’|\’$/’, ”, $values[$i]);
}
return $values;
}