How to insert the correct values ​​into the database

Question

I have jsfiddle here: http://jsfiddle.net/ybZvv/58/

Please follow the instructions in the script:

1: When you open the violin, double-click the "Add Question" button, this will add 2 lines.

2: In the first line, select the answer buttons "A" and "C", in the second line, select the answer buttons "A", "B" and "E". The text entry values ​​for each selected answer button are displayed below.

Now, what I want to do is to indicate the question number and the response value in the database.

The database should look like this:

Question table:

QuestionId (Question Number) 1 2 

Answer table:

 AnswerId (auto) QuestionId Answer 1 1 A 2 1 C 3 2 A 4 2 B 5 2 E 

What is my question: how do I send the answers and the correct question numbers to my mysqli code below so that it inserts these answers and question numbers into the Question and Answer tables?

Below I set up the mysqli / php code, but it needs to be re-accessed so that it correctly inserts the answers and the corresponding question numbers.

 $i = 0; $c = count($_POST['numQuestion']); //count number of rows for($i = 0; $i < $c; $i++ ){ $questionsql = "INSERT INTO Question (QuestionId) VALUES (?)"; if (!$insert = $mysqli->prepare($questionsql)) { // Handle errors with prepare operation here } $insert->bind_param("i", $_POST['numQuestion'][$i]); $insert->execute(); if ($insert->errno) { // Handle query error here } $insert->close(); $lastID = $mysqli->insert_id; $answersql = "INSERT INTO Answer (QuestionId, Answer) VALUES (?, ?, ?)"; if (!$insertanswer = $mysqli->prepare($answersql)) { // Handle errors with prepare operation here } $insertanswer->bind_param("is", $lastID, $_POST['value'][$i]); $insertanswer->execute(); if ($insertanswer->errno) { // Handle query error here } $insertanswer->close(); } 

I did var_dump ($ _ POST) for the above script, and this is what it produces:

 array(2) { ["numQuestion"]=> array(2) { [0]=> string(1) "1" [1]=> string(1) "2" } ["submitDetails"]=> string(14) "Submit Details" ["value"]=> array(4) { ["answerARow"]=> string(1) "A" ["answerCRow"]=> string(1) "C" ["answerBRow"]=> string(1) "B" ["answerERow"]=> string(1) "E" } } 

I get 2 errors that are identical:

Warning: mysqli_stmt :: execute (): (23000/1048): The Answer column cannot be null in /.../ on line 257 Warning: mysqli_stmt :: execute (): (23000/1048): The Answer column "cannot be null in /.../ on line 257

UPDATE:

I updated the script to include a multidimensional array, sorry I forgot to put it, but the line of code for this is below:

 var $newBtn = $(("<input class='answerBtnsRow answers' type='button' style='display:%s;' onclick='btnclick(this, " + gQuestionIndex + ");' />").replace('%s',$this.is(':visible')?'inline-block':'none')).attr('name', "value[" + gQuestionIndex + "][]").attr('value', $this.val()).attr('class', $this.attr('class')).attr('id', $this.attr('id')+'Row'); 

My suggestion is to use a multidimensional array in this format: the value [n] [], where n is the number of the question. With this new setting, you should get the following input fields:

 <input type="hidden" value="A" name="value[1][]"> <input type="hidden" value="B" name="value[1][]"> <input type="hidden" value="A" name="value[2][]"> <input type="hidden" value="C" name="value[2][]"> <input type="hidden" value="E" name="value[2][]"> 

Note that the selected value is encoded in the value attribute. The name attribute contains only the question to which the value relates.

Answer 1

It seems that $ POST does not have all the information, I suggest you change the structure of the message to associate the question with the answer, something like this:

 array(2) { ["numQuestion"]=> array(2) { [0]=> string(1) "1" [1]=> string(1) "2" } ["submitDetails"]=> string(14) "Submit Details" ["question_1"] => array(2) { [0] => string(1) "A" [1] => string(1) "C" } ["question_2"] => array(2) { [0] => string(1) "A" [1] => string(1) "B" [2] => string(1) "E" } } 

Now, if the message has this structure, you can do something like this to insert data:

 $numQuestionArray = $_POST["numQuestion"]; for($i =0; $i < count($numQuestionArray);$i++) { $questionId = $numQuestionArray[$i]; $questionsql = "INSERT INTO Question (QuestionId) VALUES (?)"; if (!$insert = $mysqli->prepare($questionsql)) { // Handle errors with prepare operation here } $insert->bind_param("i", $questionId); $insert->execute(); if ($insert->errno) { // Handle query error here } $insert->close(); $lastID = $mysqli->insert_id; $questionAnswerPostStr = "question_".$questionId; $answerArray = $_POST[$questionAnswerPostStr]; for($j =0; $j < count($numQuestionArray);$j++) { $answer = $numQuestionArray[$j]; $answersql = "INSERT INTO Answer (QuestionId, Answer) VALUES (?, ?)"; if (!$insertanswer = $mysqli->prepare($answersql)) { // Handle errors with prepare operation here } $insertanswer->bind_param("is", $lastID, $answer); $insertanswer->execute(); if ($insertanswer->errno) { // Handle query error here } $insertanswer->close(); } } 

However, I still think that question_id should automatically increase in the table instead of inserting the value of $ POST.