SitePoint Sponsor

User Tag List

Page 1 of 2 12 LastLast
Results 1 to 25 of 46

Hybrid View

  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,347
    Mentioned
    63 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,037
    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 Zealot
    Join Date
    Jan 2007
    Location
    Almere, The Netherlands
    Posts
    160
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    you will find that searching for a specific value inside the column is very, very painful
    Storing (and searching) multiple Bit values in a single column is in fact very simple

    Code SQL:
    CREATE TABLE [#checkbox](
    	[ID] [INT] IDENTITY(1,1) NOT NULL,
    	[checkbox1] [bit] NOT NULL,
    	[checkbox2] [bit] NOT NULL,
    	[checkbox3] [bit] NOT NULL,
    	[checkbox4] [bit] NOT NULL)
     
    CREATE TABLE [#bitwise](
    	[ID] [INT] IDENTITY(1,1) NOT NULL,
    	[bitwise] [INT] NOT NULL)
     
    DECLARE @checkbox1 bit
    DECLARE @checkbox2 bit
    DECLARE @checkbox3 bit
    DECLARE @checkbox4 bit
     
    SET @checkbox1 = 0
    SET @checkbox2 = 0
    SET @checkbox3 = 0
    SET @checkbox4 = 0
    INSERT INTO #checkbox (checkbox1, checkbox2, checkbox3, checkbox4) VALUES (@checkbox1, @checkbox2, @checkbox3, @checkbox4)
    INSERT INTO #bitwise (bitwise) VALUES (@checkbox1 * 1 + @checkbox2 * 2 + @checkbox3 * 4 + @checkbox4 * 8)
     
    SET @checkbox1 = 0
    SET @checkbox2 = 0
    SET @checkbox3 = 0
    SET @checkbox4 = 1
    INSERT INTO #checkbox (checkbox1, checkbox2, checkbox3, checkbox4) VALUES (@checkbox1, @checkbox2, @checkbox3, @checkbox4)
    INSERT INTO #bitwise (bitwise) VALUES (@checkbox1 * 1 + @checkbox2 * 2 + @checkbox3 * 4 + @checkbox4 * 8)
     
    SET @checkbox1 = 0
    SET @checkbox2 = 0
    SET @checkbox3 = 1
    SET @checkbox4 = 0
    INSERT INTO #checkbox (checkbox1, checkbox2, checkbox3, checkbox4) VALUES (@checkbox1, @checkbox2, @checkbox3, @checkbox4)
    INSERT INTO #bitwise (bitwise) VALUES (@checkbox1 * 1 + @checkbox2 * 2 + @checkbox3 * 4 + @checkbox4 * 8)
     
    SET @checkbox1 = 0
    SET @checkbox2 = 0
    SET @checkbox3 = 1
    SET @checkbox4 = 1
    INSERT INTO #checkbox (checkbox1, checkbox2, checkbox3, checkbox4) VALUES (@checkbox1, @checkbox2, @checkbox3, @checkbox4)
    INSERT INTO #bitwise (bitwise) VALUES (@checkbox1 * 1 + @checkbox2 * 2 + @checkbox3 * 4 + @checkbox4 * 8)
     
    SET @checkbox1 = 0
    SET @checkbox2 = 1
    SET @checkbox3 = 0
    SET @checkbox4 = 0
    INSERT INTO #checkbox (checkbox1, checkbox2, checkbox3, checkbox4) VALUES (@checkbox1, @checkbox2, @checkbox3, @checkbox4)
    INSERT INTO #bitwise (bitwise) VALUES (@checkbox1 * 1 + @checkbox2 * 2 + @checkbox3 * 4 + @checkbox4 * 8)
     
    SET @checkbox1 = 0
    SET @checkbox2 = 1
    SET @checkbox3 = 0
    SET @checkbox4 = 1
    INSERT INTO #checkbox (checkbox1, checkbox2, checkbox3, checkbox4) VALUES (@checkbox1, @checkbox2, @checkbox3, @checkbox4)
    INSERT INTO #bitwise (bitwise) VALUES (@checkbox1 * 1 + @checkbox2 * 2 + @checkbox3 * 4 + @checkbox4 * 8)
     
    SET @checkbox1 = 0
    SET @checkbox2 = 1
    SET @checkbox3 = 1
    SET @checkbox4 = 0
    INSERT INTO #checkbox (checkbox1, checkbox2, checkbox3, checkbox4) VALUES (@checkbox1, @checkbox2, @checkbox3, @checkbox4)
    INSERT INTO #bitwise (bitwise) VALUES (@checkbox1 * 1 + @checkbox2 * 2 + @checkbox3 * 4 + @checkbox4 * 8)
     
    SET @checkbox1 = 0
    SET @checkbox2 = 1
    SET @checkbox3 = 1
    SET @checkbox4 = 1
    INSERT INTO #checkbox (checkbox1, checkbox2, checkbox3, checkbox4) VALUES (@checkbox1, @checkbox2, @checkbox3, @checkbox4)
    INSERT INTO #bitwise (bitwise) VALUES (@checkbox1 * 1 + @checkbox2 * 2 + @checkbox3 * 4 + @checkbox4 * 8)
     
    SET @checkbox1 = 1
    SET @checkbox2 = 0
    SET @checkbox3 = 0
    SET @checkbox4 = 0
    INSERT INTO #checkbox (checkbox1, checkbox2, checkbox3, checkbox4) VALUES (@checkbox1, @checkbox2, @checkbox3, @checkbox4)
    INSERT INTO #bitwise (bitwise) VALUES (@checkbox1 * 1 + @checkbox2 * 2 + @checkbox3 * 4 + @checkbox4 * 8)
     
    SET @checkbox1 = 1
    SET @checkbox2 = 0
    SET @checkbox3 = 0
    SET @checkbox4 = 1
    INSERT INTO #checkbox (checkbox1, checkbox2, checkbox3, checkbox4) VALUES (@checkbox1, @checkbox2, @checkbox3, @checkbox4)
    INSERT INTO #bitwise (bitwise) VALUES (@checkbox1 * 1 + @checkbox2 * 2 + @checkbox3 * 4 + @checkbox4 * 8)
     
    SET @checkbox1 = 1
    SET @checkbox2 = 0
    SET @checkbox3 = 1
    SET @checkbox4 = 0
    INSERT INTO #checkbox (checkbox1, checkbox2, checkbox3, checkbox4) VALUES (@checkbox1, @checkbox2, @checkbox3, @checkbox4)
    INSERT INTO #bitwise (bitwise) VALUES (@checkbox1 * 1 + @checkbox2 * 2 + @checkbox3 * 4 + @checkbox4 * 8)
     
    SET @checkbox1 = 1
    SET @checkbox2 = 0
    SET @checkbox3 = 1
    SET @checkbox4 = 1
    INSERT INTO #checkbox (checkbox1, checkbox2, checkbox3, checkbox4) VALUES (@checkbox1, @checkbox2, @checkbox3, @checkbox4)
    INSERT INTO #bitwise (bitwise) VALUES (@checkbox1 * 1 + @checkbox2 * 2 + @checkbox3 * 4 + @checkbox4 * 8)
     
    SET @checkbox1 = 1
    SET @checkbox2 = 1
    SET @checkbox3 = 0
    SET @checkbox4 = 0
    INSERT INTO #checkbox (checkbox1, checkbox2, checkbox3, checkbox4) VALUES (@checkbox1, @checkbox2, @checkbox3, @checkbox4)
    INSERT INTO #bitwise (bitwise) VALUES (@checkbox1 * 1 + @checkbox2 * 2 + @checkbox3 * 4 + @checkbox4 * 8)
     
    SET @checkbox1 = 1
    SET @checkbox2 = 1
    SET @checkbox3 = 0
    SET @checkbox4 = 1
    INSERT INTO #checkbox (checkbox1, checkbox2, checkbox3, checkbox4) VALUES (@checkbox1, @checkbox2, @checkbox3, @checkbox4)
    INSERT INTO #bitwise (bitwise) VALUES (@checkbox1 * 1 + @checkbox2 * 2 + @checkbox3 * 4 + @checkbox4 * 8)
     
    SET @checkbox1 = 1
    SET @checkbox2 = 1
    SET @checkbox3 = 1
    SET @checkbox4 = 0
    INSERT INTO #checkbox (checkbox1, checkbox2, checkbox3, checkbox4) VALUES (@checkbox1, @checkbox2, @checkbox3, @checkbox4)
    INSERT INTO #bitwise (bitwise) VALUES (@checkbox1 * 1 + @checkbox2 * 2 + @checkbox3 * 4 + @checkbox4 * 8)
     
    SET @checkbox1 = 1
    SET @checkbox2 = 1
    SET @checkbox3 = 1
    SET @checkbox4 = 1
    INSERT INTO #checkbox (checkbox1, checkbox2, checkbox3, checkbox4) VALUES (@checkbox1, @checkbox2, @checkbox3, @checkbox4)
    INSERT INTO #bitwise (bitwise) VALUES (@checkbox1 * 1 + @checkbox2 * 2 + @checkbox3 * 4 + @checkbox4 * 8)
     
    SELECT checkbox1, checkbox2, checkbox3, checkbox4 FROM #checkbox
    SELECT CASE WHEN bitwise & 1 = 1 THEN 1 ELSE 0 END AS checkbox1
          ,CASE WHEN bitwise & 2 = 2 THEN 1 ELSE 0 END AS checkbox2
          ,CASE WHEN bitwise & 4 = 4 THEN 1 ELSE 0 END AS checkbox3
          ,CASE WHEN bitwise & 8 = 8 THEN 1 ELSE 0 END AS checkbox4
    FROM   #bitwise

  7. #7
    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.

  8. #8
    SitePoint Wizard bronze trophy
    Join Date
    Oct 2001
    Location
    Vancouver BC Canada
    Posts
    2,037
    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

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 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"

  10. #10
    SitePoint Wizard bronze trophy
    Join Date
    Oct 2001
    Location
    Vancouver BC Canada
    Posts
    2,037
    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

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 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"

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

  13. #13
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 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"

  14. #14
    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
    %>

  15. #15
    SitePoint Wizard bronze trophy
    Join Date
    Oct 2001
    Location
    Vancouver BC Canada
    Posts
    2,037
    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

  16. #16
    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.

  17. #17
    SitePoint Wizard bronze trophy
    Join Date
    Oct 2001
    Location
    Vancouver BC Canada
    Posts
    2,037
    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

  18. #18
    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 />

  19. #19
    SitePoint Wizard bronze trophy
    Join Date
    Oct 2001
    Location
    Vancouver BC Canada
    Posts
    2,037
    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

  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)
    Thanks Sir Andrew.I will try your code

  21. #21
    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.

  22. #22
    SitePoint Wizard siteguru's Avatar
    Join Date
    Oct 2002
    Location
    Scotland
    Posts
    3,631
    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

  23. #23
    SitePoint Wizard bronze trophy
    Join Date
    Oct 2001
    Location
    Vancouver BC Canada
    Posts
    2,037
    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

  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)
    It does say Data truncated for column 'pid' at row 1 when i try to insert multiple values for cbprob.

  25. #25
    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');


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
  •