Displaying MySQL enum values in PHP

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 <select> box.

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!

Don’t thank me, thank Super GRUB Disk!

The last thing I needed when turning my desktop back on this afternoon was GRUB merrily announcing it had experienced “Error 2”. This left my system unbootable, at a crucial time when I really really need it. Fortunately for me, I had my netbook handy, so I whipped it out and Googled the error. This told me that GRUB can’t find my hard disk. This was interesting since it’s the disk it’s on that it’s booting from, so I took comfort in the fact that the drive at least was still there.

The solution I found resided on the ever-usefulUbuntu forums, advising me to edit the GRUB menu to change the disk it’s trying to boot from. Fine, if I had GRUB set up to display a menu. Unfortunately, so such luck. Lucky for me, some months ago I had the foresight to download a burn a handy utility called Super Grub Disk. Essentially, it’s a CD (or hard drive partition) that boots into GRUB, with a load of pre-configured menu options. These do anything from trying to boot your system as best it can to re-installing GRUB to your Master Boot Record (very handy if you’ve just installed Windows to dual boot with Linux). It really is the best system recovery tool since sliced Knoppix. In particular, I chose the option to boot Linux manually, which gave me the same error as I’d seen before, then dropped me out at the boot menu for my own GRUB installation. This let me change the disk I was trying to boot from, et voil√†, one working system!

Calling PUT and DELETE on RESTful PHP services with Prototype.js

So I was asked to create a RESTful web service in PHP. No problem. I was asked to create a PHP client that connects to it through cURL. No problem. I was asked to create an AJAX interface to administer it. Problem.

The problem wasn’t the same origin policy, as the AJAX interface was to run on the same server as the service. The problem was implementing the HTTP methods.
I use Prototype.js for all of my Javascript coding. I’d recommend it to anyone, especially for AJAX as it makes your life a doddle. The basic syntax of an AJAX request using Prototype looks like this:

ajax = new Ajax.Request('test.php',{
      method: get,
      onSuccess: function(xmlHTTP) {
	$('response').update(xmlHTTP.responseText);
      }
     });

There’s a host of options for making various types of request, but that’s the gist of it. The problem with this, however, is that not all browsers support the PUT and DELETE methods, which in REST are used to update and delete records, respectively. As such, Prototype’s Ajax objects don’t try and send an XmlHttpRequest object using PUT or DELETE.
It turns out that these two methods are implemented using POST as a proxy. It then tells the web service the method you really wanted in $_POST[‘_method’]. This means that to implement calls through AJAX, where your code would have looked something like this:

if($_SERVER["REQUEST_METHOD"] == "GET") {

  echo("This looks like a GET request to me!");

} else if ($_SERVER["REQUEST_METHOD"] == "POST") {

  echo("This looks like a POST request to me!");

} else if ($_SERVER["REQUEST_METHOD"] == "PUT") {

  echo("This looks like a PUT request to me!");

} else if ($_SERVER["REQUEST_METHOD"] == "DELETE") {

  echo("This looks like a DELETE request to me!");

}

It would now need to look like this:

if($_SERVER["REQUEST_METHOD"] == "GET") { 

  echo("This looks like a GET request to me!");

} else if ($_SERVER["REQUEST_METHOD"] == "POST" && !isset($_POST['_method'])) {

  echo("This looks like a POST request to me!");

} else if ($_SERVER["REQUEST_METHOD"] == "PUT" || $_POST['_method'] == 'put') {

  echo("This looks like a PUT request to me!");

} else if ($_SERVER["REQUEST_METHOD"] == "DELETE" || $_POST['_method'] == 'delete') {

  echo("This looks like a DELETE request to me!");

}

I’m guessing that any data you’re trying to send to PUT that would normally be read in from php://input would have to by hidden in the _POST array somewhere. More experimentation required methinks!