It is not display correct numbers in the echo

Question

I have a problem with my INSERT VALUES. The problem is when I try and insert question numbers. Lets say I try to insert 2 questions. Well for first question the question number (QuestionId) should be 1 and for second question it should be 2.

But the problem is that both questions display question number 3 when I INSERT VALUES. So what it is doing for every question is inserting what would be the next question number which in this example is '3' for all questions.

Below is the example of what it is displaying at the moment when I echo $questionsql for 2 questions:

INSERT INTO Question (QuestionId, QuestionContent) VALUES ('3','what is my name'), ('3','what is my age')

The above is incorrect. Below is what it should of echoed:

INSERT INTO Question (QuestionId, QuestionContent) VALUES ('1','what is my name'), ('2','what is my age')

So what I want to know is how can I display the correct question numbers for each question, it should add the correct question number in the correct order from question 1, 2 ,3 etc.

Below is the javascript code and form code where it appends a question into a table row. The user appends a question into a table row. When user adds first question it appends question number 1 and the question, when they append their second question it appends question number 2 and the question and etc.

<script>

    function insertQuestion(form) {   

    var $tbody = $('#qandatbl > tbody'); 
    var $tr = $("<tr class='optionAndAnswer' align='center'></tr>");
    var $qid = $("<td class='qid'>" + qnum + "</td>");
    var $question = $("<td class='question'></td>");


    $('.questionTextArea').each( function() {

    var $this = $(this);
    var $questionText = $("<textarea class='textAreaQuestion'></textarea>").attr('name',$this.attr('name')+"[]")
                   .attr('value',$this.val())

    $question.append($questionText);

    });

    $tr.append($qid); 
    $tr.append($question);   
    $tbody.append($tr); 

    }

    ++qnum;
    $(".questionNum").text(qnum);
    $(".num_questions").val(qnum);

</script>


<form id="QandA" action="insertQuestion.php" method="post" >

<table id="question">
<tr>
    <th colspan="2">
        Question Number <span class="questionNum">1</span>
        <input type="hidden" class="num_questions" value="" name="numQuestion">
    </th>
</tr>
<tr>
    <td rowspan="3">Question:</td> 
    <td rowspan="3">
        <textarea class="questionTextArea" rows="5" cols="40" name="questionText"></textarea>
    </td>
</tr>
</table>

</form>

Below is the php code where it INSERT VALUES. How can I get it to display numQuestion correctly. I can't do a simple count loop because of the fact that I may have 2 exams so after creating questions for first exam, in second exam I need the question number to start at '1' again so I think it is better to correctly display the 'numQuestion'.

    $i = 0;
$c = count($_POST['gridValues']);

$insertquestion = array();

for($i = 0;  $i < $c; $i++ ){

    switch ($_POST['gridValues'][$i]){

    case "3": 
    $selected_option = "A-C";
    break;

    case "4": 
    $selected_option = "A-D";
    break;

    default:
    $selected_option = "";
    break;

    }      

    $optionquery = "SELECT OptionId FROM Option_Table WHERE (OptionType = '". mysql_real_escape_string($selected_option)."')";
    $optionrs = mysql_query($optionquery);
    $optionrecord = mysql_fetch_array($optionrs);
    $optionid = $optionrecord['OptionId']; 

    $insertquestion[] = "'". 
                    mysql_real_escape_string( $_POST['numQuestion'] ) ."','".  
                    mysql_real_escape_string( $_POST['questionText'][$i] ) ."','".  
                    mysql_real_escape_string( $optionid ) ."'";

}

 $questionsql = "INSERT INTO Question (SessionId, QuestionId, QuestionContent, QuestionMarks, OptionId) 
    VALUES (" . implode('), (', $insertquestion) . ")";

echo($questionsql);


mysql_close();

The questionId is a composite key but I do not want it auto incremented. I really just want to get the values of numQuestion. Please forget the auto increment and tell me how to just get the numQuestion value please if any of you knows :) — user1309180, Apr 05 '12 at 13:54
@Bondye, I tried to limit the code so that you don't have to see the huge amount of code and then saves people asking me what this means, and what that is lol :) I do actually have a conditional statement in my code, I will post the conditional statement. if you want to know what 'questionText' is, it is each question (actual question) it goes through. — user1309180, Apr 05 '12 at 14:11
I updated php code. 'gridValues' is the option type selected for each question but don't worry about the options and all that. Just foces on the numQuestion part — user1309180, Apr 05 '12 at 14:15

Mar Cel · Answer 1 · 2012-04-05T13:45:17.940

you dont have to generate these question numbers. try using an auto increment sequence in the database. you are confusing some things here. You need a unique identifier in your database, which is a different thing as the numbers of the table in your frontend. also, if you have different exams with different questions, you should have 2 tables, one holding you exams, and one holding your questions with an auto increment id and a foreign key that references the exams it belongs to. In this scenario you dont have to commit the question ids but only query the question name (and the exam refernce), the database will generate your Id. the question count value in your table needs to be independent from your database Id.

score 0 · Answer 2 · answered Apr 05 '12 at 13:36

If it is a primary key then leave the field as it is like this

INSERT INTO Question (QuestionContent) VALUES ('what is my name'), ('what is my age')

it will automatically assign the id to the question. When you get data to display you can simply use a variable to produce question numbers. You will have to avoid using id as question number.

It is not display correct numbers in the echo

2 Answers2