SitePoint Sponsor

User Tag List

Results 1 to 11 of 11
  1. #1
    SitePoint Member
    Join Date
    Nov 2006
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Insert Checkbox array into a MySql database

    Hi folks,

    Can anyone help me, I have a form with allot of checkboxes. The checkboxes are all named the same (Question[]) and I am storing the value in an array with one variable ($Question = $_POST["Question"]

    I am posting this to a MySql insert statement on another page, I can echo the results out ok on the page I have posted to, using the foreach statement.

    But I am having trouble inserting the data into the database, I can put in my own values into the statement and that works fine but using the foreach statement with the posted values doesn't seem to work. I have also tried to loop around the values with a for loop and I am again able to echo the values out.

    I have searched through a lot of sites and other forums on this subject, and have come across this problem on many forums, however I can't seem to get the insert to work.

    I am still effectively learning PHP, and I would appreciate any help on this matter.

    Many thanks

    Bob

  2. #2
    SitePoint Wizard silver trophy Jelena's Avatar
    Join Date
    Feb 2005
    Location
    Universum, 3rd Corner
    Posts
    3,000
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Can you share the code you have (even if it doesn't work) with us?
    -- Jelena --

  3. #3
    SitePoint Wizard silver trophy
    Join Date
    Mar 2006
    Posts
    6,132
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i just posted something similar, see if it helps you.
    specifically, the last block of php code in my post.
    http://www.sitepoint.com/forums/showthread.php?t=440030

  4. #4
    SitePoint Member
    Join Date
    Nov 2006
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Jelena
    Can you share the code you have (even if it doesn't work) with us?
    I have tried different variations on the two scripts below

    $Question = $_POST["Question"];

    $n = count($Question)-1;
    for($i = 0; $i <= $n; $i++)

    {
    $query = "INSERT INTO questions VALUES (' ', $Question[$i])";

    mysql_query($query);

    ///////////////////////////////////////////////////////////////////////////
    foreach ($Question as $f)
    {
    echo $f."<br />";


    $query = "INSERT INTO questions VALUES (' ', '$f')";


    mysql_query($query);
    }

    ?>

  5. #5
    SitePoint Wizard cranial-bore's Avatar
    Join Date
    Jan 2002
    Location
    Australia
    Posts
    2,634
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    How about this..
    PHP Code:
    $query "INSERT INTO `questions` (question) VALUES ";
    foreach(
    $_POST['Question'] as $Q) {
      
    $Q mysql_real_escape_string($Q);
      
    $query.= "('$Q'), ";
    }
    $query rtrim($query", ");
    if(
    mysql_query($query)) echo "Questions were inserted";
    else echo 
    mysql_error(); 
    What this script does is build a single query to insert all of the questions in one go. It's more effecient that doing them one by one, Also it's best not to use an empty string for your auto-incrment field -- the query will not work if you upgrade to MySQL5. Instead list only the fields you are actually inserting to.

    An example of the SQL query this script generates might look like this:
    Code:
    INSERT INTO `questions` (question) VALUES 
    ('What is a dog'),
    ('How much is a ninja worth?'),
    ('Do shoes really cause cancer?'),
    ('What\'s the opposite of Thursday?')

  6. #6
    SitePoint Member
    Join Date
    Nov 2006
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by cranial-bore
    How about this..
    PHP Code:
    $query "INSERT INTO `questions` (question) VALUES ";
    foreach(
    $_POST['Question'] as $Q) {
      
    $Q mysql_real_escape_string($Q);
      
    $query.= "('$Q'), ";
    }
    $query rtrim($query", ");
    if(
    mysql_query($query)) echo "Questions were inserted";
    else echo 
    mysql_error(); 
    What this script does is build a single query to insert all of the questions in one go. It's more effecient that doing them one by one, Also it's best not to use an empty string for your auto-incrment field -- the query will not work if you upgrade to MySQL5. Instead list only the fields you are actually inserting to.

    An example of the SQL query this script generates might look like this:
    Code:
    INSERT INTO `questions` (question) VALUES 
    ('What is a dog'),
    ('How much is a ninja worth?'),
    ('Do shoes really cause cancer?'),
    ('What\'s the opposite of Thursday?')
    Thanks for the code, have got it working but not quite how I wanted it, I should of mentioned I am working with 6 checkboxes (this is just a test as it will be over 40 boxes when I get it working). Your script seems to insert the data into one column but I need it to go across an entire row i.e. q1, q2, q3 etc

    What happened when I inserted it with just the first field (q1) it copied the results down

    row q1 q2 q3 q4
    1 result
    2 result
    3 result
    etc
    Which is what you probably would of expected.

    So I tried it with (q1,q2,q3 etc) and I now get an error

    Column count doesn't match value count at row 1 ;

    Thanks again for having a look at my problem

    Bobby boy

  7. #7
    SitePoint Wizard cranial-bore's Avatar
    Join Date
    Jan 2002
    Location
    Australia
    Posts
    2,634
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    How many fields does your table have? Up to Q4?
    If you're planning on going to Q40 on one table you should normalize your DB design and have a separate questions table (1:M relationship)

  8. #8
    SitePoint Wizard silver trophy
    Join Date
    Mar 2006
    Posts
    6,132
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    a one to many relationship is the way to go, espescially if you will have a variable number of questions. not that you absolutel must do it that way, but its a better approach.

  9. #9
    SitePoint Member
    Join Date
    Nov 2006
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by cranial-bore
    How many fields does your table have? Up to Q4?
    If you're planning on going to Q40 on one table you should normalize your DB design and have a separate questions table (1:M relationship)

    At the moment I have 7 fields with 1 id field and 6 further fields for the answers from the questions, there can only be one answer for each question, see the link below.

    Note this is just a test page to get the functionality working, when you submit it, it is posting back to itself and I am just echoing the results out.

    I will also have patient table which I will normalise and link it to the results of the form table.

    I know that 40 + checkboxes sound a bit daft but it is for a friend who runs a complementry therapy business and current mails the form to clients when they first book an appointment. You only have to fill the form out once, even if you have further consultations.

    Have a look and see what you think, I is just a test page!

    http://gary.digitalroutes.co.uk/spri.../testform2.php


    Thanks for your help!


    Bobby boy

  10. #10
    SitePoint Wizard cranial-bore's Avatar
    Join Date
    Jan 2002
    Location
    Australia
    Posts
    2,634
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I typed a better response, but it was lost.

    Basically these should be radio buttons, not checkboxes. Don't use JS to reimplement native browser bahavior.
    Give ALL radios the name question, but give each set of answers a array key in the HTML:
    Code:
    Question 3
    <input type="radio" name="question[3]" value="in the past">
    <input type="radio" name="question[3]" value="sometimes">
    <input type="radio" name="question[3]" value="often">
    PHP will then get $_POST['question'] which is an array of answers, the key relating to each question.
    $_POST['question'][3] will be the response to the third question.

    You can use a single table if the questions will not be changing, however a 1:M relationship, with a separate table is still a more maintanable and flexible approach.
    You might need to do a search for database normalization if the concept isn't familiar to you.

  11. #11
    SitePoint Wizard silver trophy
    Join Date
    Mar 2006
    Posts
    6,132
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    keep in mind users can have javascript disabled. if they do, and they dont check one of the answers for one of your questions, it will screw up your index associations for all other questions after it. if they omit an answer for the 3rd question, your script will think the answer they provided for the 4th question is the answer to the 3rd, and the answer for the 4th will really be the 5th and so on. try it.

    this is because a browser will not send the field name/value if a checkbox is not "checked".

    take a different approach
    Code:
    <input type="checkbox" name="data[q1][past]">
    <input type="checkbox" name="data[q1][sometimes]">
    <input type="checkbox" name="data[q1][often]">
    
    <input type="checkbox" name="data[q2][past]">
    <input type="checkbox" name="data[q2][sometimes]">
    <input type="checkbox" name="data[q2][often]">
    
    etc...
    edit- good call cranial-bore on using radio buttons instead.


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
  •