Reputation: 459
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>
Upvotes: 0
Views: 75
Reputation: 26480
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';
}
mysqli::insert_id
mysqli::prepare
mysqli-stmt::insert_id
Upvotes: 1
Reputation: 81
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
Upvotes: 1