Hi All,

Below, I have listed a method of storing a "multiple" select options in a database, but I am not sure if this is a good method. If not, please give me some suggestion or other methods to do so(like using checkboxes instead?).

I have 6 options that an user can choose from from a select menu. Out of the 6 options, I want them to be able to pick a maximum of 4 options.

Therefore, what I did is to include the "multiple" attribute to the <select> object. So, my codes will now looks like this:


<select name="agency" multiple>
<option>Accounting &amp; Auditing</option>
<option>Administrative &amp; Office Support</option>
<option>Airport</option>
<option>Animal Care</option>
<option>Health</option>
<option>Courts</option>
</select>

Now my question is, what is the best method to store this in a database, so that it will make searching easy? Also, how should my database be designed? Should I have separate columns in the database for each of the 6 options, or should I store all 4 of the selected options in ONE column?

Do you get the idea of what I am trying to do?

Thank you.

Leon

I would suggest you to go for either of these ways.

1) Store them all in one varchar column seperated by comma's. Search can be done using LIKE operator.

2) Store them all in different rows in a different table, and then relating this from the other table using a foreignkey. Search has to be done using JOINs which would be little round about. I feel this would make things complex.

Re your post : Storing them in different column is actually a good idea by having each of BIT type and storing FALSE as default and make them true only if selected from the SELECT list. SEARCH can be done easily with checking for TRUE in each of the columns. But the only disadvantage is when anymore item(s) is/are to be added to the SELECT list or want to remove one among those 6 items in future, then that would require altering the the table structure by adding/removing those many columns again. I would not prefer that having future developments in mind. If no items expected to be added/removed in the future(these 6 items are fixed), then I would go for that.

To keep it simple, my vote is for method 1.

Re MULTIPLE attribute : When keeping the user friendlyness, I would choose to go for CHECK boxes, so that the user doesn't have to be educated to press some key and select the item that he wants to select or de-select. And check boxes always depict that there can be multiple items selected.

Tips: If you choose to go with CHECKBOXes then name them all with same name, so that just a REQUEST.FORM("chkCONTROLNAME") would return all selected values seperated by COMMA. Only other thing you have to do is to validate for atleast 4 items selected.

Hope that helps
Cheers!
_________________________
- Vijay G
Strive for Perfection

Vijay, thank you VERY, VERY for your help!!! It is always helpful to get advice from experts. I'll definitely use method one. Regarding using checkboxes Vs drop-down, I'll have to see how many select options my client wants; I think she might have a lot more than 10 items. But your advices are great.

Thanks again.

Leon