Categories
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() )
	{
		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"
}