Checkboxes Inserting Into MySql Database

Question

Hi guys,

I have a survey type form, and in a number of the questions, the user has the option to tick more than one box.

I am storing these answers in a mysql database however at the moment, the database only stores the very last checkbox that is ticked. Is there anyway to store all the checked values, possible separated by a coma, or semi-colon?

Here is my code:

        $q1 = mysql_escape_string($_POST['q1']);
        $q2 = mysql_escape_string($_POST['q2']);
        $q3 = mysql_escape_string($_POST['q3']);            
        $q4 = mysql_escape_string($_POST['q4']);
        $q5 = mysql_escape_string($_POST['q5']);            
        $q6 = mysql_escape_string($_POST['q6']);
        $q7 = mysql_escape_string($_POST['q7']);
        $q8 = mysql_escape_string($_POST['q8']);
        $q9 = mysql_escape_string($_POST['q9']);
        $q10 = mysql_escape_string($_POST['q10']);
        $q11 = mysql_escape_string($_POST['q11']);
        $q12 = mysql_escape_string($_POST['q12']);
        $q13 = mysql_escape_string($_POST['q13']);
        $q14 = mysql_escape_string($_POST['q14']);
        $email = mysql_escape_string($_POST['email']);

        require_once('connection.php');

        $sql="INSERT INTO survey (Question1, Question2, Question3, Question4, Question5, Question6, Question7, Question8, Question9, Question10, Question11, Question12, Question13, Question14, eMail) VALUES ('$q1', '$q2', '$q3', '$q4', '$q5', '$q6', '$q7', '$q8', '$q9', '$q10', '$q11', '$q12', '$q13', '$q14', '$email')";

        if (!mysql_query($sql,$conn))
          {
      die('Error: ' . mysql_error());
          }

          mysql_close($conn);

Answer 1

First, mysql_escape_string is depreciated - you should use mysql_real_escape_string.

Secondly, this would allow anyone malicious to insert different values into the results, such as 2's and 3's. You need to make every value conform to 0 or 1. To do that, I'd recommend you cast to a bool then an int:

$q1 = (int)((bool)$_POST['q1']);
$q2 = (int)((bool)$_POST['q2']);
...

For your HTML, each checkbox needs a value attribute of '1'.

Since these are integer only, there is no need to escape them.

Answer 2

If the checkbox is not checked, the it is not sent to the next page. I belive that $_POST['email'] is a text field and this is sent nomatter if it is something fullfield.
Instead, try sometring like this:

$sql="INSERT INTO survey (Question1, Question2, Question3, Question4, Question5, Question6, Question7, Question8, Question9, Question10, Question11, Question12, Question13, Question14, eMail) VALUES (";

if ($_POST['q1'] == 'On') $sql .= "1, "; else $sql .= "0, ";

if ($_POST['q2'] == 'On') $sql .= "1, "; else $sql .= "0, ";
if ($_POST['q3'] == 'On') $sql .= "1, "; else $sql .= "0, ";
if ($_POST['q4'] == 'On') $sql .= "1, "; else $sql .= "0, ";
if ($_POST['q5'] == 'On') $sql .= "1, "; else $sql .= "0, ";
if ($_POST['q6'] == 'On') $sql .= "1, "; else $sql .= "0, ";
if ($_POST['q7'] == 'On') $sql .= "1, "; else $sql .= "0, ";
if ($_POST['q8'] == 'On') $sql .= "1, "; else $sql .= "0, ";
if ($_POST['q9'] == 'On') $sql .= "1, "; else $sql .= "0, ";
if ($_POST['q10'] == 'On') $sql .= "1, "; else $sql .= "0, ";
if ($_POST['q11'] == 'On') $sql .= "1, "; else $sql .= "0, ";
if ($_POST['q12'] == 'On') $sql .= "1, "; else $sql .= "0, ";
if ($_POST['q13'] == 'On') $sql .= "1, "; else $sql .= "0, ";
if ($_POST['q14'] == 'On') $sql .= "1, "; else $sql .= "0, ";

$sql .= mysql_escape_string($_POST['email']) . ')';

        require_once('connection.php');

        if (!mysql_query($sql,$conn))
          {
      die('Error: ' . mysql_error());
          }

          mysql_close($conn);

Answer 3

at the moment, the database only stores the very last checkbox that is ticked.

This sounds like it could be a name collision in your input field names. Check that your input fields are named uniquely.

Is there anyway to store all the checked values, possible separated by a coma, or semi-colon?

You seem to have separate database columns for each question: why would you want to join them with commas or semicolons?

Answer 4

Have you checked the name of your html checkbox element? All the checkbox elements belonging to a certain question (eg: 1) should have name like: 'question1[]' . This way, all the values would be returnt through $_POST as an array.

for example, $_POST["question1"] now is an array containing the checked values.

edit--

ok I just noticed that you have one checkbox for every question, so this doesnt cause the problem.