Search This Blog

Monday, June 6, 2011

ENUM Options and Field Defaults

You already encountered ENUM type fields in the preceding chapter, when you included the sex field in the users table. That field could take either one of two values: M or F. For the sake of a nice substantial example, you're now going to modify the userposition field so that it also limits entries to specific, preset values.

The field type for userposition was originally defined as VARCHAR (50) NOT NULL. Assuming that all future users will come from one of the four countries already entered in the field, redefine the field like this:

mysql> ALTER TABLE user MODIFY userposition
    -> ENUM('Mid','Link', 'Winger','Utility Back') DEFAULT 'Utility Back';
Query OK, 6 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
If you've added your own records to the table, you'll probably want to modify these values to match your own. All being well, the values previously contained in this field remain intact.
Pop quiz! Which of the PHP functions gives you the default value of a table field? The answer to this lies in the table presented earlier. The def property of the object returned by the mysql_fetch_field function gives you the default value of any field if one is specified. You can add the following lines to the last example:

if($field_info_object->def) echo "<br><b>Default Value;
$field_info_object->def</b> ";

Getting a list of options available in an ENUM type field is a little more complicated. Issue the following query:

mysql> SHOW COLUMNS FROM user LIKE 'userposition';
And here's what you get:
+--------------+--------------------------+------+-----+-------------+-------+
| Field        | Type                     | Null | Key | Default     |  Extra|
+--------------+--------------------------+------+-----+-------------+-------+
+ userposition | enum('Mid','Link',       |      |     |             |       |
|              | 'Winger','Utility Back') | YES  |     | Utility Back|       |
+--------------+--------------------------+------+-----+-------------+-------+
1 row in set (0.02 sec)
This shows that the Type information actually contains the values you want. Extracting them is a little tricky, but not too hard once you know how.

Try it Out: Get ENUM Options
Start example
The following script produces an array whose elements contain each option. The last element of the array holds the default value from the set. Enter this code into a file called enum_options.php: 

<?php
include_once "./common_db.inc";
$link_id = db_connect();
mysql_select_db("sample_db");

$query = "SHOW COLUMNS FROM user LIKE 'userposition' ";
$result = mysql_query($query);
$query_data = mysql_fetch_array($result);

if(eregi("('.*')", $query_data["Type"], $match))
   $enum_str = ereg_replace(" ' ", "", $match[1]);
   $enum_options = explode(',', $enum_str);
}
echo "ENUM options with the default value:<BR>";
foreach($enum_options as $value){
   echo "-$value<BR>";
}
echo "<BR>Default: Value: <b>$query_data[Default]</b>";
echo "<P>";

?>



End example

How it Works

You start by issuing a SQL query that finds the record containing the definition of the field userposition. This data is fetched in the form of an associative array called $query_data: 

$query "SHOW COLUMNS FROM user LIKE 'userposition'";
$result = mysql_query($query);
$query_data = mysql_fetch_array($result);
Field definition entries for Type and Default can now be accessed as array entries $query_data['Type'] and $query_data['Default'], respectively. The former should now contain a string that looks like this:

enum('Mid','Link','Winger','Utility Back')
To extract the option strings, you must eliminate the preceding string enum, braces and enclosing single quotes. First, use eregi() with the regular expression ('.*') to match everything inside the brackets:

eregi("('.*')", $query_data["Type"], $match)
The third argument is an array that holds matches for the given pattern—its second element, $match[1], contains the string you want:

'Mid','Link','Winger','Utility Back'
Assuming this goes to plan, strip off single quotes by calling the eregi_replace() function, and place the modified string in $enum_str: 

$enum_str = eregi_replace("'", "", $match[1]);
which should look like this:

Mid, Link, Winger, Utility Back
Then explode() the $enum_str variable so that its comma-separated values can be put into the array $enum_options: 

$enum_options = explode(',', $enum_str);
This array now contains each option from the ENUM field as a separate element.
The rest of the script simply steps through the elements in $enum_options and echoes them out. Once you've gone through all the enum values, you echo out the default value in bold font:

echo "ENUM options with the default value:<BR>";
foreach($enum_options as $value)
  echo "-$value<BR>";
{
echo "<BR>Default Value: <b>$query_data [Default]</b>";
echo "<P>";
You can put this example into a function that returns an array containing each option from a specified ENUM field. Add the following code to the end of the common include file common_db.inc: 

function enum_options($field, $link_id)
{
   $query = "SHOW COLUMNS FROM user LIKE '$field'";
   $result = mysql_query($query, $link_id);
   $query_data =mysql_fetch_array($result);
   if(eregi("('.*')", $query_data["Type"], $match)) {
      $enum_str = ereg_replace("'", "", $match[1]);
      $enum_options = explode(',', $enum_str);
      return $enum_options;
   }else{
      return 0;
   }
}
You can test it with the following script called test_enum.php: 

<?php
include_once "./common_db.inc";
$link_id = db_connect();
mysql_select_db("sample_db");

$array = enum_options('userposition', $link_id);
foreach($array as $var){
   echo $var,"<BR>";
}
?>
Labels: