How to use a mySQL ENUM type field to Populate an HTML drop down list

PHP
1

I have a column with an ENUM type in a table in my mySQL database.

What I would like to do is populate an HTML drop down list with PHP using all of the options available from the ENUM field.

For example, if the ENUM options of the column were:

Category 1
Category 2
Category 3

I would like to output it into an HTML dropdown list via PHP to get this result:


<option name="Category 1">Category 1</option>
<option name="Category 2">Category 2</option>
<option name="Category 3">Category 3</option>

Any help greatly appreciated.

2

Something like this? :

<select name="select_menu">
<option label="no title" value="1000" selected></option>
<?php

//get info from database
$result = mysql_query("select my_column from my_database where whatever_limiteations");
    if (!$result) {
        echo("<p>Error performing query:" . mysql_error() . "<p>");
        exit();
    }

//put data in menu
while ( $row = mysql_fetch_array($result)) {
    $my_column = $row["my_column"];
    echo("<option name='$my_column' value='$my_column'>$my_column</option>");
}
?>
</select>
3

that won’t work for values that aren’t assigned samsm. :slight_smile:

peDey, i think the answer lies on this page: www.mysql.com/doc/E/N/ENUM.html

If you want to get all possible values for an ENUM column, you should use: SHOW COLUMNS FROM table_name LIKE enum_column_name and parse the ENUM definition in the second column.

4

Oh, of course! The possible enum values not the values in the enum column. I totally missed the point.

5

In response to DR_LaRRY_PEpPeR:

That works a treat - thanks!

Another question relating to that -

the result of my query is:


enum('Equipment','Set','Show')

QUESTION: what is the best way of extracting ONLY the enum values from the result string, and then putting them into an array?

Once the ENUM values are in a PHP array, I can loop through the array and output the values as select options.

6

i had to think about this for for a sec. :slight_smile: try this. remember, $enum would actually be the text from the query. i’m just assigning what you showed for this example:

$enum = "enum('Equipment','Set','Show')";

// Strip "enum('" off the front
// and "')" off the end
$enum = substr($enum, 6, -2);

// Split the rest up on ','
// $values is an array of the possible values
$values = explode("','", $enum);

then just loop through $values to make the <select> list. :slight_smile:

now, that explode() part wouldn’t work correctly if one of your actual ENUM values had a single quote on both sides of a comma. :slight_smile: but i don’t think that’s likely.

also, if you have any single quotes in the ENUM values, they will be doubled. you could fix that by using this first:

$enum = str_replace("''", "'", $enum);
7

Nice One _LaRRY_PEpPeR.

I omitted substr() altogether.

Instead, I used str_replace() twice for cleaning up the ENUM string before putting it into an array. I’m sure that there are smarter ways of getting the same results, but it works nicely.

this is the code that I used:


<?php

// get ENUM values String from specified DB table column using my Database Class
// (using substituted table and col names.)
$DB = new Database();
$sql = "SHOW COLUMNS FROM tbl_the_table LIKE 'category'";
$DB->get_data($sql);
while($DB->row = mysql_fetch_array($DB->result_id)) {
	$type = $DB->row["Type"];
}

// format the values
// $type currently has the value of: enum('Equipment','Set','Show')


// ouput will be: Equipment','Set','Show')
$output = str_replace("enum('", "", $type);

 // $output will now be: Equipment','Set','Show
$output = str_replace("')", "", $output);

// array $results contains the ENUM values
$results = explode("','", $output);

// create HTML select object
echo"<select name=\\"the_name\\">\
";

// now output the array items as HTML Options
for($i = 0; $i < count($results); $i++) {
	echo "<option value=\\"$results[$i]\\">$results[$i]</option>\
";
}

// close HTML select object
echo"</select>";
?>

Thanks for the help!

8