I am trying to get all possible values for an enum field (mysql). Here's the query:

SHOW COLUMNS FROM tbl_name LIKE field_name

which returns a string

enum('yes','no')

I am trying to do a regex match to extract the values yes,no from the string.

I've had no luck. I've looked through various online docs and nothing that seems to address my specific problem of extracting items from (). I may have missed something somewhere.

thanks in advance,

  • Keith

    I wouldn't use regex for this (some may disagree with me), I'd just use string functions to break down the string. e.g.

    1. use substr() to get rid of the enum(' and ') at the beginning and end of the string (note that I've included the single quotes).

    2. Then use explode and use ',' as the delimiter (with the single quotes).

    Diego

      if you simply want to spool all of the possible choices in a enum field into an array:

      $enum_array = mysql_fetch_assoc (mysql_query ("DESCRIBE table_name column_name"));
      $enum_string = str_replace ('enum', '$enum_array = array ', $enum_array['Type']);
      eval ($enum_string . ';');
      

      $enum_array now has all of the enum choices

      i realize that many don't like the eval() function...

        To round things off, here is a solution that actually does use regular expressions. As always, however, it's a matter of which solution is the better performer, which is easier to read, and who's going to be the poor sod who has to modify it in six months' time.

        Assumes that $string contains the "enum(this,that,tother)" string. The fact that it starts with "enum" doesn't really matter.

        preg_match_all('/(?<=[(,])([^,)]+)(?=[,)])/', $string, $matches);
        $enum_contents = $matches[1];
        

        If you're not certain that what gets returned from the query will always be of the appropriate form:

        if(preg_match_all('/(?<=[(,])([^,)]+)(?=[,)])/', $string, $matches))
          $enum_contents = $matches[1];
        else
          $enum_contents = array();
        

          Thanks for the input. I actually found a regex on alt.php that did the trick. I used it in a function that takes an enum string as a parameter and outputs the values to a Drop down menu.

          
          function EnumDDMenu($enumString,$fieldName,$selected=0)
          {
          $enumArray = explode(",", str_replace("'", "",preg_replace("/enum\((.+)\)/", "\\1", $enumString)));
          print "<select name=\"" . $fieldName . "\">\n";
          foreach($enumArray as $key => $value){
          	print "<option value=\"" . $value . "\"";
          	if($value == $selected){ print " selected"; }
          	print ">" . $value . "</option>\n";
          }
          print "</select>\n";
          }//END function EnumDDMenu
          

          I've used it in some forms and it is so much easier than hard-coding the forms with the values.

          Thanks,

          • Keith
            Write a Reply...