PHP: how to fetch all possible values of an ENUM from MySQL
April 4th, 2009 by adam
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"
}
Loading ...
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;
}