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() )
	{
		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 thought on “PHP: how to fetch all possible values of an ENUM from MySQL

  1. Cmyker

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

Leave a Reply

Your email address will not be published. Required fields are marked *