04-17-2009, 09:51 PM
1-) What's the best way for storing avaiable (selectable) checkboxes data in Mysql? (would it be in a single table record... or maybe a field for each option...?)
2-) What's the best way for storing selected checkboxes data in Mysql?
I've tried doing this once: having a field in a table only for those checkboxes that were selected by the user, but eventually, i had headaches to manage those... but that doesnt matter, there's probably a better way for managing them, so, how would you do it?
04-17-2009, 10:08 PM
Personally I would normalize. The way to do that is to look at the checkboxes like they are child attributes to a parent object and give these attributes their own table with a foreign key pointing back to the parent table.
The attribute table would have a simple structure: Foreign key to parent, attribute key, and attribute value. You could even omit the attribute value, if you decided to store only those checkboxes that were "on". I would probably store all checkbox values, with a 0 or 1 (on or off).
04-17-2009, 10:23 PM
If the set of checkbox values come from another DB table, then there's a great technique for both getting all checkboxes--*with* previously checked ones checked--and setting the values back into the "many to one" table.
Below is ASP code. Should be able to easily adapt it to PHP or JSP or whatever.
To get all the checkbox values and at the same time indicate which ones were previously checked:
SQL = "SELECT I.itemid, I.itemname, IF(A.itemid IS NULL,'','selected') AS sel " _
& " FROM CheckboxItems AS I LEFT JOIN UserAnswers AS A " _
& " ON I.itemid = A.itemid AND A.userid = " & userid _
& " ORDER BY I.itemname "
Set RS = conn.Execute(SQL)
Do Until RS.EOF
<input type=checkbox name="answers" value="<%=RS("itemid")%>" <%=RS("sel")%> ><%=RS("itemname")%>
You see it? The LEFT JOIN says "get all the possible checkbox items and mark an item as 'selected' if the current user has that item in his/her set of answers."
Then to update the table, it's nearly as easy:
' In ASP, this next line will get a string that is a list of all the CHECKED
' checkbox values, with comma-space delimiters between values.
' Adapt to match the system you are using:
answers = Request("answers")
' first, delete all the prior answers for this user:
conn.Execute "DELETE FROM answers WHERE userid = " & userid
' then insert all new records, recording current set of answers:
SQL = "INSERT INTO answers (userid, itemid) " _
& " SELECT " & userid & ", itemid FROM items " _
& " WHERE itemid IN (" & answers & ")"
Suppose the user checked checkboxes with itemid values of 17, 23, and 37. And suppose the current userid is 77183
Then that SQL insert becomes
INSERT INTO answers ( userid, itemid )
SELECT 77183, itemid FROM items WHERE itemid IN (17, 23, 37)
You can't normally insert an array or set of values into a table. But if you SELECT a group of values--which just happen to be the same values as are in your IN( ) list!--you can do it as above.