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, 09: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, 10: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, 10: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:
<% 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:
<% ' 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
INSERT INTO answers ( userid, itemid ) SELECT 77183, itemid FROM items WHERE itemid IN (17, 23, 37)