Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
Thread: Questions regarding checkboxes
04-17-2009, 10:51 PM #1
- Join Date
- Mar 2009
- Thanked 3 Times in 3 Posts
Questions regarding checkboxes
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, 11:08 PM #2
- Join Date
- Dec 2005
- Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
- Thanked 637 Times in 625 Posts
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, 11:23 PM #3
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:
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."Code:<% 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")%> <% RS.MoveNext Loop %>
Then to update the table, it's nearly as easy:
Suppose the user checked checkboxes with itemid values of 17, 23, and 37. And suppose the current userid is 77183Code:<% ' 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 & ")" conn.Execute SQL ... %>
Then that SQL insert becomes
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.Code:INSERT INTO answers ( userid, itemid ) SELECT 77183, itemid FROM items WHERE itemid IN (17, 23, 37)