SitePoint Sponsor

User Tag List

Page 1 of 2 12 LastLast
Results 1 to 25 of 46
  1. #1
    SitePoint Enthusiast joeyramirez's Avatar
    Join Date
    Nov 2011
    Location
    Cabanatuan City, Philippines
    Posts
    56
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    storing multiple values within one field

    Hi,

    How to insert multiple checkbox values(from asp classic) into one column of the database(mysql)?.

    Suppose if a checkbox contain 4 values,if i choose 3 fvalues,then how to insert that 3 values into a single column.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,275
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    don't do it

    multiple values in a single column violates first normal form

    you will find that searching for a specific value inside the column is very, very painful

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast joeyramirez's Avatar
    Join Date
    Nov 2011
    Location
    Cabanatuan City, Philippines
    Posts
    56
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks sir r937. . I'll think of another way to solve my problem.

  4. #4
    SitePoint Wizard bronze trophy
    Join Date
    Oct 2001
    Location
    Vancouver BC Canada
    Posts
    2,030
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)
    Yes, you are way better off having a related table to store your checkbox values in.

    So Table A is the main table with an ID for whatever record is in it and then Table B has a couple of columns, one for the ID (of the record in Table A), another for Checkbox_Value (1, 2, 3, 4).
    Andrew Wasson | www.lunadesign.org
    Principal / Internet Development

  5. #5
    SitePoint Enthusiast joeyramirez's Avatar
    Join Date
    Nov 2011
    Location
    Cabanatuan City, Philippines
    Posts
    56
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Can I ask you how to insert multiple row values in one SUBMIT?for example I have cbprob=1,cbprob=2,cbprob=3 and my query is
    INSERT INTO tbltest (pid) VALUES ('"&Request.Form("cbprob")&"'). How am i going to insert the three values?

  6. #6
    SitePoint Enthusiast joeyramirez's Avatar
    Join Date
    Nov 2011
    Location
    Cabanatuan City, Philippines
    Posts
    56
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    how to insert multiple checkbox values in database

    Can someone teach me how to insert multiple values from a checkbox in one submit?My checkboxes are dynamic.

  7. #7
    SitePoint Wizard bronze trophy
    Join Date
    Oct 2001
    Location
    Vancouver BC Canada
    Posts
    2,030
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)
    It's pretty hard to teach you how to do something when we don't really know anything about what you are trying to achieve. What database are you using, and what is the structure of the table you're inserting into? Also what does the form field html look like and are you posting (POST) or getting (GET) the results?

    In the big picture, you'll need to collect all of the checkbox results from your form and then put them into some sort of array or series of variables. Then, if you're following my suggestion of using a related table for the checkbox results, you'll need to create a loop and insert each result so that there is a separate row for each checkbox.
    Andrew Wasson | www.lunadesign.org
    Principal / Internet Development

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,275
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by awasson View Post
    ... you'll need to create a loop and insert each result so that there is a separate row for each checkbox.
    make sure you generate only one INSERT statement, and just loop over the values, rather than generating a new INSERT statement for each loop iteration

    i.e. instead of this --

    INSERT INTO ... VALUES ( ... );
    INSERT INTO ... VALUES ( ... );
    INSERT INTO ... VALUES ( ... );
    INSERT INTO ... VALUES ( ... );

    do this instead --

    INSERT INTO ... VALUES
    ( ... ),( ... ),( ... ),( ... );
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Wizard bronze trophy
    Join Date
    Oct 2001
    Location
    Vancouver BC Canada
    Posts
    2,030
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)
    @r937: Does that statement work with MS Access? I'm not sure what joeyramirez is using by my hunch is it might be Access.
    Andrew Wasson | www.lunadesign.org
    Principal / Internet Development

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,275
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by awasson View Post
    @r937: Does that statement work with MS Access?
    nope, i don't think so

    but he's using mysql (see post #1)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Addict
    Join Date
    Apr 2009
    Posts
    357
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Doug G
    =====
    "If you ain't the lead dog, the view is always the same - Anon

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,275
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Doug G View Post
    Works in Access too
    are you sure?

    i don't have msaccess to actually test this, but that documentation page you linked to says...
    Use an additional INSERT INTO statement with a VALUES clause for each additional record you want to create.
    what i suggested above inserts multiple rows with one statement

    INSERT INTO ... VALUES
    ( ... ),( ... ),( ... ),( ... ); -- each of these parentheses creates a row and contains multiple column values
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    SitePoint Enthusiast joeyramirez's Avatar
    Join Date
    Nov 2011
    Location
    Cabanatuan City, Philippines
    Posts
    56
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    my page looks like this.this is test2.asp where the checkboxes are located.

    <form action="test3.asp" method="post">

    <%
    Dim sql,rs
    sql="Select * from tblproblem ORDER by pID"
    Set rs=Server.CreateObject("ADODB.RECORDSET")
    rs.Open sql,Conn,1,3
    %>

    <%While Not rs.EOF%>
    <input name="cboprob" type="checkbox" value="<%=rs("pID")%>" /><%=rs("pname")%><br />
    <%
    rs.Movenext
    Wend
    Set rs=nothing
    %>
    <input type=submit name=submit />
    </form>

    and this is test3.asp where the insert query is located


    <%
    Dim sql,exec
    sql="INSERT INTO tbltest (pid) VALUES ('"&Request.Form("cbprob")&"')"
    Set exec=Conn.Execute(sql)
    If err.number=0 then
    Response.Write "Save Completed"
    End If
    Conn.Close
    Set exec=nothing
    %>

  14. #14
    SitePoint Wizard bronze trophy
    Join Date
    Oct 2001
    Location
    Vancouver BC Canada
    Posts
    2,030
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)
    @joeyramirez: Can you show a sample of what the checkboxes look like (in HTML) as they render from that code? It looks to me like they will all have the same name (cboprob) and we need to make sure that they are unique so they can be requested and inserted into a SQL insert query.

    Thanks,
    Andrrew
    Andrew Wasson | www.lunadesign.org
    Principal / Internet Development

  15. #15
    SitePoint Enthusiast joeyramirez's Avatar
    Join Date
    Nov 2011
    Location
    Cabanatuan City, Philippines
    Posts
    56
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    sitepoint.jpg



    This is what it looks like.

  16. #16
    SitePoint Wizard bronze trophy
    Join Date
    Oct 2001
    Location
    Vancouver BC Canada
    Posts
    2,030
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)
    Thanks, can you look at the html and find out what the name attributes are for the checkboxes? My concern is that they're all the same value in which case we need to make them unique somehow. Maybe doing something like the following:

    Code:
    <%
    i = 0
    While Not rs.EOF    
    %>
    <input name="cboprob-<%=i%>" type="checkbox" value="<%=rs("pID")%>" /><%=rs("pname")%><br />
    <%
        rs.Movenext
        i = i+1
    Wend
    Set rs=nothing
    %>
    Andrew Wasson | www.lunadesign.org
    Principal / Internet Development

  17. #17
    SitePoint Enthusiast joeyramirez's Avatar
    Join Date
    Nov 2011
    Location
    Cabanatuan City, Philippines
    Posts
    56
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    <input name="cboprob" type="checkbox" value="1" />Monitor No Output<br />



    <input name="cboprob" type="checkbox" value="2" />Monitor No Power<br />



    <input name="cboprob" type="checkbox" value="3" />Monitor Blurred<br />



    <input name="cboprob" type="checkbox" value="4" />CPU No Power<br />



    <input name="cboprob" type="checkbox" value="5" />Blue Screen<br />

  18. #18
    SitePoint Wizard bronze trophy
    Join Date
    Oct 2001
    Location
    Vancouver BC Canada
    Posts
    2,030
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)
    Ok, that's going to be a problem... You need to name each checkbox with a unique name otherwise when you try to request the form variable, you won't be able to distinguish between them.

    In the little code snippet I posted I was just appending the variable with a number so the html would look something like this:
    HTML Code:
    <input name="cboprob-0" type="checkbox" value="1" />Monitor No Output<br />
    
    <input name="cboprob-1" type="checkbox" value="2" />Monitor No Power<br />
    
    <input name="cboprob-2" type="checkbox" value="3" />Monitor Blurred<br />
    
    <input name="cboprob-3" type="checkbox" value="4" />CPU No Power<br />
    
    <input name="cboprob-4" type="checkbox" value="5" />Blue Screen<br />

    Then you could get the variables on the receiving asp script as follows

    Code:
    <%
    checkbox-0 = request("cboprob-0")
    checkbox-1 = request("cboprob-1")
    checkbox-2 = request("cboprob-2")
    checkbox-3 = request("cboprob-3")
    checkbox-4 = request("cboprob-4")
    
    %>
    Then you could create a query to insert the results sort of like this:

    Code:
    <%
    
    sql="INSERT INTO tbltest (pid) VALUES ('&checkbox-0&'),('&checkbox-1&'),('&checkbox-2&'),('&checkbox-3&'),('&checkbox-4&')"
    
    %>
    This is roughly how I would do it so it will require some tweaking, particularly (checking for syntax errors I may have introduced) and adding some sort of ID to accompany the inserted cboprob values so you can relate that to a particular customer or trouble ticket.

    I have to head out for a while but I will check back later.

    Andrew
    Andrew Wasson | www.lunadesign.org
    Principal / Internet Development

  19. #19
    SitePoint Enthusiast joeyramirez's Avatar
    Join Date
    Nov 2011
    Location
    Cabanatuan City, Philippines
    Posts
    56
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks Sir Andrew.I will try your code

  20. #20
    SitePoint Enthusiast joeyramirez's Avatar
    Join Date
    Nov 2011
    Location
    Cabanatuan City, Philippines
    Posts
    56
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    By the way Sir Andrew, all cboprob are accompanied by a ticket number. I'm just trying to add the checkbox values to the DB.

  21. #21
    SitePoint Wizard siteguru's Avatar
    Join Date
    Oct 2002
    Location
    Scotland
    Posts
    3,629
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Can't remember, but ... if multiple checkboxes have the same name, does Request.Form("cboprob") return a comma-separated string of values?
    Ian Anderson
    www.siteguru.co.uk

  22. #22
    SitePoint Wizard bronze trophy
    Join Date
    Oct 2001
    Location
    Vancouver BC Canada
    Posts
    2,030
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by siteguru View Post
    Can't remember, but ... if multiple checkboxes have the same name, does Request.Form("cboprob") return a comma-separated string of values?
    Wow, I haven't tried that but it sure would be a cool trick if it does.
    Andrew Wasson | www.lunadesign.org
    Principal / Internet Development

  23. #23
    SitePoint Enthusiast joeyramirez's Avatar
    Join Date
    Nov 2011
    Location
    Cabanatuan City, Philippines
    Posts
    56
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It does say Data truncated for column 'pid' at row 1 when i try to insert multiple values for cbprob.

  24. #24
    SitePoint Enthusiast joeyramirez's Avatar
    Join Date
    Nov 2011
    Location
    Cabanatuan City, Philippines
    Posts
    56
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    How do I split this values 1, 3, 4, 5 to be inserted like INSERT INTO tbltest (pid) VALUES ('1),('3'),('4'),('5');

  25. #25
    SitePoint Wizard bronze trophy
    Join Date
    Oct 2001
    Location
    Vancouver BC Canada
    Posts
    2,030
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)
    Debugging ASP can be a bit of a pain.

    Comment out the the actual ASP statement that inserts the data so that you avoid the error. It's the one that starts with "Set" and then says something about the Object Recordset.

    Next we want to look at what the SQL string looks like so somewhere the commented out insert statement put in:
    Code:
     
    
    Response.Write sql
    Where "sql" is the SQL string that you are inserting.
    Andrew Wasson | www.lunadesign.org
    Principal / Internet Development


Bookmarks

Posting Permissions

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