Insert Checkbox array into a MySql database

PHP
1

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”]:wink:

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

Can you share the code you have (even if it doesn’t work) with us?

3

i just posted something similar, see if it helps you.
specifically, the last block of php code in my post.

4

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

How about this…


$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:


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

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

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

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

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/springfield/testform2.php

Thanks for your help!

Bobby boy

10

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:


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

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


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