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
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.
Thanks I’ll fix that.
Bad written in – echo “”; – it must be ‘$column_name’
thank you so much for this! saved me a lot of headaches.
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.
You should specify the TABLE_SCHEMA also if your MySQL install has more than one database type. You could have a column with the same name in another database that is not type ENUM, and it could cause problems.
$result = mysql_query(“SELECT COLUMN_TYPE FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = ‘$db_name’ AND TABLE_NAME = ‘$table_name’ AND COLUMN_NAME = ‘$column_name'”)
or die (mysql_error());
Also, I realize that this is old and just a tutorial, but we should no longer be using the mysql_ libraries, as they are deprecated. Use MySQLi or a PDO abstraction layer.
Thanks, I’m working on writing mysqli versions of all of my tutorials too.
nice, thanks author!
This helped me a lot, thank you so much!
Thanks Bro! It working!
Thats great, thx – how do i then save the selected option and update…?