I’ve always been in the habit of using MySQL’s enum data types, allowing you to define a set of values allowed in a particular field. This saves having to create an extra table which only has 2 fields while still having a strict set of values for a field. The problem with this over the other option is that you can’t just do a SELECT query to get all the available options for the field, for example to display in an HTML

After some googling I turned up a HowTo by Bill Heaton with a reasonable solution. However, there’s 2 niggles I had with his solution: it uses a load of string processing functions when it could use a single Regular Expression, and it’s a function, not a class.

The solution I’ve got in practice uses a “query” class I wrote, but for the example I’ll use plain old mysql_query():

class enum_values {
	
	public $values;
	
	public function __construct($table, $column){
		
		$sql = "SHOW COLUMNS FROM $table LIKE '$column'";
		if ($result = mysql_query($sql)) { // If the query's successful
			
			$enum = mysql_fetch_object($result);
			preg_match_all("/'([\w ]*)'/", $enum->Type, $values);
			$this->values = $values[1];
			
		} else {
			
			die("Unable to fetch enum values: ".mysql_error());
			
		}
		
	}
}

Let’s have a look at that code. If you’re not familiar with OOP in PHP 5, we start off by creating a class called “enum_values” with 1 property ($values) and a constructor that accepts 2 arguments, the table and column we’re getting the values from. This constructor is called when we create an instance of the class (an object), e.g.

$example = new enum_values('table_name', 'column_name');

Right, so within the constructor with have a “SHOW COLUMNS” query. This will show us the structure of a specified column in a specified table. The fields returned include “Name” (containing the column’s name) and “Type”, which contains the column’s data type, and in the case of enum, it’s possible values. This is the field we’re interested in.

To get at the values field we use mysql_fetch_object to create an object containing the fields and their values. We could equally use mysql_fetch_array here, but I like objects.

The next line is where the magic happens. The string we’ve got to work with in the Type field looks like:

enum('value1','value2','value3')

Bill’s solution suggests using substr to cut the string down to the list of values within the brackets (after finding the position with strpos), using explode to split the resulting string into an array by using the commas as delimeters, then looping through the array and removing the quotes around each value with str_replace. But wouldn’t it be a lot nicer if we could just extract the values without the quotes in the first place?

The way I solved this was with preg_match_all, which has the wonderful ability to do all this in a single line. Firstly I needed a regular expression that matched characters in single quotes. The regex ‘([\w ]*)’ will match a single quote, followed by any number of alphanumeric characters and spaces, followed by another single quote. Note that I haven’t used a . to allow any character inside the quotes, since this would allow single quotes, meaning the entire string would be matched. The parentheses are used to “group” the characters inside the quotes, meaning we can refer back to them.

The regex is then delimited with slashes (to show PHP it’s a regex), then double quotes (since we need to pass it as a string). The resulting argument passed to the function is “/’([\w ]*)’/”.

The second argument is simply the string we’re operating on, $enum->Type. The third argument is a variable that’s going to store every match found in an array. But it’s even better than that. What we actually get is a multidimensional array, with [0] containing an array of the entire matched string, and [1] containing the contents of the first “group” within that match. If there was a second group, it would be in [2], a third in [3], etc. So while $values[0][0] would contain ‘value1’ with the single quotes, $values[1][0] would simply contain value1, as the characters inside the quotes were grouped.

Now we’ve got our array of values we store it in the object’s $values property (note that it’s referred to as $this->values, it’s not the same as the $values array I was just referring to). Now we can access an array of the values like this:

// Db connection stuff goes here
$example = new enum_values('table_name', 'column_name');
print_r($example->values);

Lovely!