Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    Regular Coder
    Join Date
    Mar 2009
    Posts
    120
    Thanks
    13
    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?

  • #2
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    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).

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,225
    Thanks
    80
    Thanked 4,456 Times in 4,421 Posts
    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:
    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
    %>
    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:
    Code:
    <%
    ' 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
    ...
    %>
    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
    Code:
        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.

    Sneaky enough?


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •