PHP Tutorial: Put MySQL ENUM values into drop down select box

Comments 5 Standard

If you have an ENUM field in your table schema then you don’t want to go through the hassle of re-writing out all of the different ENUM values when you do an online form or CMS update system. This piece of code will take all of your ENUM values and put them into a select box so the choices displayed in the drop down are always up to date.

$table_name = "your_table_name"
$column_name = "field_with_enum_options";

echo "<select name=\"$column_name\">";
$result = mysql_query("SELECT COLUMN_TYPE FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = '$table_name' AND COLUMN_NAME = '$column_name'")
or die (mysql_error());

$row = mysql_fetch_array($result);
$enumList = explode(",", str_replace("'", "", substr($row['COLUMN_TYPE'], 5, (strlen($row['COLUMN_TYPE'])-6))));

foreach($enumList as $value)
    echo "<option value=\"$value\">$value</option>";

echo "</select>";
enumList

Want to use a function instead? Try this:

function enumDropdown($table_name, $column_name, $echo = false)
{
   $selectDropdown = "<select name=\"$column_name\">";
   $result = mysql_query("SELECT COLUMN_TYPE FROM INFORMATION_SCHEMA.COLUMNS
       WHERE TABLE_NAME = '$table_name' AND COLUMN_NAME = '$column_name'")
   or die (mysql_error());

    $row = mysql_fetch_array($result);
    $enumList = explode(",", str_replace("'", "", substr($row['COLUMN_TYPE'], 5, (strlen($row['COLUMN_TYPE'])-6))));

    foreach($enumList as $value)
         $selectDropdown .= "<option value=\"$value\">$value</option>";

    $selectDropdown .= "</select>";

    if ($echo)
        echo $selectDropdown;

    return $selectDropdown;
}
enumList
About these ads

5 thoughts on “PHP Tutorial: Put MySQL ENUM values into drop down select box

  1. Thanks for this tutorial although there’s a minor problem in your sql : the variables with tablename and columnname are inversed.

    Never the less, this saved me some time of trial and error.

    Thx.

  2. Reblogged this on dayuloli and commented:
    This is a great tutorial for anything wanting to effortlessly put all ENUM values in their MySQL database field into a drop-down list. I am using this to make a list of countries.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s