Not inserting the correct data into database while adding a record in php

Question

Trying to inserting a record into database but the data is not inserting correctly into database tables.

Here are the Database Tables:

Questions Database Table:

question_number  question_no  text  status
1                 1           html    1
2                 2           php     0

Choices Database Table:

id   question_number   is_correct  text   status
1       1                1        abc       1
2       1                0        abc       1
3       1                0        abc       1
4       1                0        abc       1 
5       2                0        abcdfd    0
6       2                1        abcdsf    0
7       2                0        abcdsf    0
8       2                0        abcsdfg   0

If i am trying to add a new record it is inserting in this format in database tables

Question table:

question_number  question_no  text  status
3                2           html    1

Choices Database Table:

id   question_number   is_correct  text          status
9        2             1           abchdfgh        1
10       2             0           abfdhdfc        1
11       2             0           afdhgbc         1
12       2             0           adfhbc          1

Here is it inserting question number as 2 in choices table but it should insert as 3 here.

Here is the code for that:

<?php
if(isset($_POST['submit'])){
    //Get post variables
    $question_number = $_POST['question_no'];
    $question_text = $_POST['question_text'];
    $correct_choice = $_POST['correct_choice'];
    //Choices array
    $choices = array();
    $choices[1] = $_POST['choice1'];
    $choices[2] = $_POST['choice2'];
    $choices[3] = $_POST['choice3'];
    $choices[4] = $_POST['choice4'];
    $choices[5] = $_POST['choice5'];

    //Question query
    $query = "INSERT INTO `questions`(question_no, text)
                VALUES('$question_number','$question_text')";
            //print_r($query);
            //die();                
    //Run query
    $insert_row = $mysqli->query($query) or die($mysqli->error.__LINE__);

    //Validate insert
    if($insert_row){
        foreach($choices as $choice => $value){
            if($value != ''){
                if($correct_choice == $choice){
                    $is_correct = 1;
                } else {
                    $is_correct = 0;
                }
                //Choice query
                $query = "INSERT INTO `choices` (question_number, is_correct, text)
                        VALUES ('$question_number','$is_correct','$value')";

                //Run query
                $insert_row = $mysqli->query($query) or die($mysqli->error.__LINE__);

                //Validate insert
                if($insert_row){
                    continue;
                } else {
                    die('Error : ('.$mysqli->errno . ') '. $mysqli->error);
                }
            }
        }
        $msg = 'Question has been added';
    }
}

/*
* Get total questions
*/
$query = "SELECT * FROM `questions` WHERE status='1'";
//Get The Results
$questions = $mysqli->query($query) or die($mysqli->error.__LINE__);
$total = $questions->num_rows;
$next = $total+1;
?>
        <form method="post" action="add.php">
            <p>
                <label>Question Number: </label>
                <input type="number" value="<?php echo $next; ?>" name="question_no" />
            </p>
            <p>
                <label>Question Text: </label>
                <input type="text" name="question_text" required/>
            </p>
            <p>
                <label>Choice #1: </label>
                <input type="text" name="choice1" required/>
            </p>
            <p>
                <label>Choice #2: </label>
                <input type="text" name="choice2" required/>
            </p>
            <p>
                <label>Choice #3: </label>
                <input type="text" name="choice3" required/>
            </p>
            <p>
                <label>Choice #4: </label>
                <input type="text" name="choice4" required/>
            </p>
            <p>
                <label>Choice #5: </label>
                <input type="text" name="choice5" required/>
            </p>
            <p>
                <label>Correct Choice Number: </label>
                <input type="number" name="correct_choice" />
            </p>
            <p>
                <input type="submit" name="submit" value="Submit" />
            </p>
        </form>

Have you checked where this goes wrong? You could simply dump the SQL queries before they are executed to check if the generation does not work — Nico Haase, Mar 14 '19 at 11:06
@NicoHaase it was going here in the insertion of choices table //Choice query $query = "INSERT INTO `choices` (question_number, is_correct, text) VALUES ('$question_number','$is_correct','$value')"; — tester, Mar 14 '19 at 11:09
What is the difference between `question_number` and `question_no`? Is one of them the auto incremented ID? — Qirel, Mar 14 '19 at 11:11
So, have you dumped all executed `INSERT` queries and checked them? — Nico Haase, Mar 14 '19 at 11:13
question_number is the id which is autoincremented and question_no is the question number which i am inserting — tester, Mar 14 '19 at 11:13
@Qirel question_id is nothing but which i have kept as question_number — tester, Mar 14 '19 at 11:14

Qirel · Answer 1 · 2019-03-14T11:26:22.680

Since your question_number is the auto-incremented ID from the inserted question, that's the value you need to insert into the question_number column of your new row in choices.

Fetch the last inserted id by doing

$insert_row->insert_id;

I have modified your code to simplify it a bit, and converted it to using a prepared statement, which you should do for your questions insert as well.

Using a prepared statement has additional benefits when inserting multiple rows in a loop, as you are. You prepare the query once, and execute it many times.

if ($insert_row) {
    $question_number = $insert_row->insert_id;

    $query = "INSERT INTO `choices` (question_number, is_correct, text) VALUES (?, ?, ?)";
    $stmt = $mysqli->prepare($query) or die($mysqli->error.__LINE__);
    foreach ($choices as $choice => $value) {
        if (!empty($value) {
            $is_correct = (int)($correct_choice == $choice);
            //Choice query

            //Run query
            $stmt->bind_param('sss', $question_number, $is_correct, $value);
            $stmt->execute();

            if ($stmt->errno) {
                die('Error : ('.$stmt->errno . ') '. $stmt->error);
            }
        }
    }
    $stmt->close();
    $msg = 'Question has been added';
}

PHP.net on mysqli::insert_id
PHP.net on mysqli::prepare
PHP.net on mysqli-stmt::insert_id
(if you convert your first query to a prepared statement too)
How can I prevent SQL injection in PHP?

Hi, you are missing a param in `$stmt->bind_param('sss', $is_correct, $value);` — RiggsFolly, Mar 14 '19 at 11:20
@Qirel i have one doubt why you have mentioned sss in bind_param — tester, Mar 14 '19 at 11:37
It's the type of the variable associated with that binding. `s` means string, if you're dealing with integers, it's `i`. So, technically, yours should be `iis`, since `$question_number` and `$is_correct` is an integer, while `$value` is a string. — Qirel, Mar 14 '19 at 11:42

score 1 · Answer 2 · answered Mar 14 '19 at 11:20

1

in your choice query you inserting $question_number but it should be your last insert id that you insert in question table for last insert id use this $last_id = mysqli_insert_id($conn);

//Updated Choice query
  $query = "INSERT INTO `choices` (question_number, is_correct, text)
                    VALUES ('$last_id','$is_correct','$value')";

Hope this helps

answered Mar 14 '19 at 11:20

Ajay Namdev

81
5

After adding in this inserting question_number as zero for choices table – tester Mar 14 '19 at 12:00

score 0 · Accepted Answer · answered Mar 19 '19 at 10:20

0

$last_id = $mysqli->insert_id;

Worked with this query

answered Mar 19 '19 at 10:20

tester

429
5
20

Not inserting the correct data into database while adding a record in php

3 Answers3