jtf2012 Posted January 12, 2012 Share Posted January 12, 2012 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! Link to comment Share on other sites More sharing options...
Antonio Conte Posted January 16, 2012 Share Posted January 16, 2012 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. 1 Link to comment Share on other sites More sharing options...
artsyL Posted July 2, 2013 Share Posted July 2, 2013 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>"; ?> Link to comment Share on other sites More sharing options...
margaux Posted July 2, 2013 Share Posted July 2, 2013 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>"; 3 Link to comment Share on other sites More sharing options...
artsyL Posted July 2, 2013 Share Posted July 2, 2013 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! 1 Link to comment Share on other sites More sharing options...
margaux Posted July 2, 2013 Share Posted July 2, 2013 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. 3 Link to comment Share on other sites More sharing options...
artsyL Posted July 9, 2013 Share Posted July 9, 2013 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; } 1 Link to comment Share on other sites More sharing options...
Recommended Posts