How to get all possible values of an ENUM field

22 August 2011

Sometimes it's a little overkill to create an extra table and connecting table to add a parameter to an object. An ENUM field comes in handy, but how about getting these values when setting the field?

You can create an array with all ENUM options, but this is dangerous, because you have to make changes on multiple places (in the code and in the database), so it's better to fetch the ENUM options from the field and display them in a list to be selected.

To get information about a specific field in the DB, use this query:

SHOW COLUMNS FROM `table` LIKE 'column_name'

This returns an object containing a variable (Type) with the ENUM options. If you parse these you can get an array with all values. This function gets all values and returns them in an array:

public function getCategories() {
  $query = "SHOW COLUMNS FROM `pages` LIKE 'category'";
  $result = $this->dcd->fetchObject($query);
  $result = str_replace(array("enum('", "')", "''"), array('', '', "'"), $result->Type);
  $arr = explode("','", $result);
  return $arr;
}

Loop through the result array, and there you've got your select with all posible options:

<?
$cats = $page->getCategories();
foreach ($cats AS $cat) {
  echo '<option value="'.$cat.'" '.($page->category == $cat?"selected":"").'>'.$cat.'</option>';
}
?>
You must have JavaScript enabled to use this form!

Leave a comment!

  1. Your mail is safe with me. It's only only used to display your Gravatar image!

0 comments

Please feel free to be the first to comment on this page!

By placing a comment you let me know i'm doing a good job. It doesn't have to be constructive, just a "awesome!" makes me really happy! You contribute to a better world!