Use Data Type Enum For Form Drop-Down Options

[jtf2012]

Hello,

 

2nd time poster here and just learning MySQL and PHP using the 4th Edition.

 

I've got my DB setup for a mobile web app that will basically used to enter in attributes of different items, let's call them widgets, gadgets and frazzles. I am trying to determine the best way to store the options available for certain attributes. Some attributes will require a number input, which isn't the problem. Many of the attributes will be a selection of one of 3-7 choices. In order to keep my tables normalized, my thought is to create an attrib_value table and store the options using Data Type ENUM and then use a foreign key to link the attribute to the proper attribute values and have them displayed as a pull-down in a form.

 

lMy tables are like this

 

Table1: item_type

id_item_type INT NN UN AI

item_name VARCHAR() NN

item_descrip VARCHAR ()

 

Table2: item_attrib

id_item_attrib INT NN UN AI

fk_id_item_type INT NN

attrib_name VARCHAR() NN

attrib_units VARCHAR()

 

Table3_attrib_value

id_attrib_value INT NN UN AI

fk_item_attrib INT NN

attrib_value ENUM('x','y','z') NN

 

There is a 1:N identifying key between the item_attrib table and the attrib_value table. Does this make sense to do it this way or is there a better way that I am not seeing?

 

Thanks everyone for your insight!

[Antonio Conte]

I've never used ENUM with a Database, but it does make sense from what I've learned by JAVA.

 

After reading about ENUM in the MySQL doc, I believe this is very good way to solve your problem.

 

If Enum doesn't do the trick, build a "legal values" array and check input with in_array() or something like that.

[artsyL]

I have a database with many ENUM values, so I want my forms to have dynamic dropdown menus with the ENUM values. I have found some information on how to create these, but none of them use mysqli, so (as a newbie) I'm at a bit of a loss.

 

This is the code I have so far. I feel that I'm close, but I can't seem to make the magic happen.

 

<select name="coll_type"><option>Select one</option>
<?
$q ="SELECT TRIM(TRAILING ')' FROM TRIM(LEADING '(' FROM TRIM(LEADING 'enum' FROM column_type))) column_type FROM information_schema.columns WHERE table_name = 'collection' AND column_name = 'PENDING'";
$r = mysqli_query ($dbc, $q);

mysqli_fetch_array($r);
$enumList = explode(",", str_replace(array('', '', "'"), $r));
foreach($enumList as $value)
    echo "<option value=\"$value\">$value</option>";
    
    echo "</select>";       
?>

[margaux]

You're close. You have not stored your result anywhere with mysqli_fetch_array, so you won't be able to access the returned data.

$table_name = "collection";
$column_name = "PENDING";

echo "<select name=\"$column_name\"><option>Select one</option>";
$q = "SELECT COLUMN_TYPE FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = '$table_name' AND COLUMN_NAME = '$column_name'";
$r = mysqli_query($dbc, $q);

$row = mysqli_fetch_array($r);

$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>";

[artsyL]

Thank you so much! I had actually tried this tutorial earlier today and I must have twisted that thing around a hundred times, but just couldn't make it cough up the array.

 

Woo-hoo! on to another struggle!

[margaux]

Glad I could provide a little help. When I'm struggling with some code, I use var_dump and print_r to see what is being returned. Then I know how to manipulate the data.

[artsyL]

Just in case someone might be interested, I thought I would share the jquery functionality I added:

 

I wanted certain input fields to show/hide depending on what was selected. I found the trick was to change the input to text. It is also important to set the CSS display to none.

 

Here is the PHP:

echo "<p>Please select collection type: <select id=selectEnum class=\"box\" type=\"text\" name=\"coll_type\">";
		$q = "SELECT COLUMN_TYPE FROM INFORMATION_SCHEMA.COLUMNS
			WHERE TABLE_NAME = 'collection' AND COLUMN_NAME = 'coll_type'";
		$r = mysqli_query($dbc, $q);
		
		$row = mysqli_fetch_array($r);
		
		$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></p>";	

Here is the Jquery:

<script>
$(document).ready(function() {
  $.enumList = {
    'select' : $([]),
    'personal' : $([]),
    'family' : $([]),
    'institutional' : $('#corporate'),
    'corporate' : $('#corporate'),
    'other' : $('#other')
  };

  $('#selectEnum').change(function() {
    // hide all
    $.each($.enumList, function() { this.hide(); });
    // show current
    $.enumList[$(this).val()].show();
  });
});
</script>

Here is the CSS:

#other {
	display:none;	
}

#corporate {
	display: none;	
}