PHP programming

PHP: how to fetch all possible values of an ENUM from MySQL

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() )
		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"

1 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;

Comments are closed.