Retrieve valid entries for a MySQL ENUM/SET column

November 23rd, 2005

Here's a handy little PHP function I threw together to retrieve valid entries for a MySQL ENUM/SET column, handy for generating dynamic radio group/checkbox/multiple select HTML elements:

PLAIN TEXT
PHP:
  1. // grab possible SET/ENUM values and return an array
  2. function getPossibleValues($table,$field){
  3.  
  4.     $query = "SHOW COLUMNS FROM `$table` LIKE '$field'";
  5.     $result = mysql_query($query);
  6.    
  7.     if(mysql_num_rows($result)>0){
  8.         list(,$fields) = mysql_fetch_row($result);
  9.         $options = explode("','",preg_replace("/(enum|set)('(.+?)')/","\2",$fields));
  10.         return $options;
  11.     } else {
  12.         return array();
  13.         return false;
  14.     }
  15.  
  16. }


 Add to del.icio.us    Digg this    Technorati

Related Posts:

Entry Filed under: PHP, MySQL

4 Comments Add your own

  • 1. dan  |  January 11th, 2006 at 1:56 pm

    Although it doesn't this doesn't affect the correctness of the script, there should be a semicolon after "return $options"

  • 2. Harvey A. Ramer  |  June 24th, 2006 at 1:46 pm

    Thanks for this code snippet. It does exactly what I want!

    I do have one small problem though. On my system the function returns a string like this for the opening array value:


    set('value

    And for the closing value it returns:


    value')

    Do you have a quick suggestion for how to make the preg_replace function work? I'm on PHP 4.

  • 3. Domenico  |  August 18th, 2006 at 10:39 am

    This work for me:

    preg_replace("/(?:enum|set)('(.+?)')/","\1", $fields)

  • 4. Passerby  |  March 5th, 2008 at 3:46 pm

    None of the solutions suggest prior to this post have worked for me, so I came up with this:

    preg_replace("/')$/", '', preg_replace("/^(enum|set)('/", '', $fields))

    Note: I'm using PHP 5.2.0 with mysqli functions

    This function is a great idea though. Thanks for sharing.

Leave a Comment

Required

Required, hidden

Some HTML allowed:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <code> <em> <i> <strike> <strong>

Trackback this post  |  Subscribe to the comments via RSS Feed

Calendar

November 2005
M T W T F S S
    Dec »
 123456
78910111213
14151617181920
21222324252627
282930  

Most Recent Posts