Build dropdown from Mysql SET or ENUM fields


We need to populate dropdown fields from Mysql SET or ENUM fields pretty often. So here is how I use to do it.
Lets say ‘test’ is a table that has a field, ‘agree’ of type ‘SET’ having values ‘Yes’ and ‘No’. On the front end of my application I want to show a dropdown/select field having options ‘Yes’ and ‘No’.

First get the field’s ‘SET’ values. This is a function I wrote for myself to get the ENUM field values.

function getEnumFieldValues($tableName, $fieldName){
$field_query = mysql_query("show columns from $tableName where Field='$fieldName'");
if(mysql_num_rows($field_query) <= 0) return false; $fieldDetail = mysql_fetch_array($field_query); $type = preg_replace('/(^set\()|(^enum\()/i', '', $fieldDetail['Type']); $enumFields = substr($type, 0, -1); $fieldSplit = split(',', $enumFields); return $fieldSplit; }

Using the values, populate the dropdown field like this.

$enumFields = getEnumFieldValues('agree', 'test');
echo '<select name="agree">';
foreach($enumFields as $value){
echo '<option value="' . $value . '">' . $value . '</option>';
}
echo '</select>';

That's all. Now put any table and field names to get the ENUM/SET field values and create your dropdown.

,

  1. #1 by Benjamin on January 14, 2010 - 6:11 pm

    Super, works a treat, thanks!

  2. #2 by Imran on January 14, 2010 - 9:13 pm

    Good to know that :-)

  3. #3 by Junnjun M. on January 29, 2010 - 1:17 pm

    Hi there…How can I remove the qoutes (”) that wrap values??Thanks

  4. #4 by Imran on January 29, 2010 - 8:41 pm

    Handling quotes is always a bit tricky. You need to convert quotes to html entities, which means use the code like following:

    $enumFields = getEnumFieldValues('agree', 'test');
    echo '<select name="agree">';
    foreach($enumFields as $value){
    echo '<option value="' . htmlentities($value) . '">' . $value . '</option>';
    }
    echo '</select>';
    

    When you are using the submitted value in your php script, get the value like this:

    $value = html_entity_decode($value);

    Hope this helps.

  5. #5 by qwerty on July 26, 2010 - 10:02 pm

    Junnjun M. :
    Hi there…How can I remove the qoutes (”) that wrap values??Thanks

    Add : $fieldSplit = str_replace (‘\”, ”, $fieldSplit);
    before return line

  6. #6 by Aneeq on May 12, 2012 - 4:44 pm

    This is an easy to use code to select records from MySQL database table and display in dropdown combo box using PHP.

    $cn=mysql_connect($db_host,$db_user,$db_password) or die(mysql_error());
    mysql_select_db($db_name,$cn) or die(mysql_error());
    $sql = “SELECT field_name FROM table_name”;
    $rs = mysql_query($sql) or die(mysql_error());
    echo “”;
    while($row = mysql_fetch_array($rs)){
    echo “”.$row[“field_name”].””;
    }mysql_free_result($rs);
    echo “”;

    Source:
    http://phphelp.co/2012/05/10/how-to-fill-

    a-dropdown-combo-box-in-php-from-mysql-database-table/

    OR

    http://addr.pk/a8cf

  7. #7 by Emmanuel on August 4, 2012 - 3:55 pm

    Hi. htmlentities seems not working with the code and i dont understand why do you have to use the preg_replase function. am new to php a little explanation would be appreciated

  8. #8 by Emmanuel on August 4, 2012 - 6:57 pm

    i was able to remove the the single quotes with str_replace() function like
    print ” .$option=str_replace(“‘”,””,$value);
    but i want to retain the selected option after pressing the submit button such as if a user put invalid data in other text input and submit the form then the select option remain with the selected choice.

  9. #9 by Emmanuel on August 4, 2012 - 6:58 pm

    i was able to remove the the single quotes with str_replace() function like
    print \'<option value=\"\’ . $value . \’\">\’ .$option=str_replace(\"\’\",\"\",$value);
    but i want to retain the selected option after pressing the submit button such as if a user put invalid data in other text input and submit the form then the select option remain with the selected choice.

  10. #10 by Emmanuel on August 4, 2012 - 6:59 pm

    i was able to remove the the single quotes with str_replace() function like
    print \\\’&lt;option value=\\&quot;\\\’ . $value . \\\’\\&quot;&gt;\\\’ .$option=str_replace(\\&quot;\\\’\\&quot;,\\&quot;\\&quot;,$value);
    but i want to retain the selected option after pressing the submit button such as if a user put invalid data in other text input and submit the form then the select option remain with the selected choice.

  11. #11 by Imran on August 4, 2012 - 8:15 pm

    Hi Emmanuel,

    Try the folllowing code

    $enumFields = getEnumFieldValues('agree', 'test');
    echo '<select name="agree">';
    foreach($enumFields as $value){
    echo '<option ' . ($_POST['agree'] == $value ? 'selected ' : '')  . 'value="' . $value . '">' . $value . '</option>';
    }
    echo '</select>';
    
(will not be published)