how to accept two correct answers in MySQL query?

Question

I have four choices and two of them can be both the correct answer.

if($isCorrect == "answer1"){
    $sql2 = mysql_query("INSERT INTO answers (question_id, answer, correct) VALUES ('$lastId', '$answer1', '1')")or die(mysql_error());
    mysql_query("INSERT INTO answers (question_id, answer, correct) VALUES ('$lastId', '$answer2', '0')")or die(mysql_error());
    mysql_query("INSERT INTO answers (question_id, answer, correct) VALUES ('$lastId', '$answer3', '0')")or die(mysql_error());
    mysql_query("INSERT INTO answers (question_id, answer, correct) VALUES ('$lastId', '$answer4', '0')")or die(mysql_error());
    $msg = 'You questions has been succesfully added!';
  header('location: addQuestions.php?msg='.$msg.'');
exit();
}
if($isCorrect == "answer2"){
    $sql2 = mysql_query("INSERT INTO answers (question_id, answer, correct) VALUES ('$lastId', '$answer2', '1')")or die(mysql_error());
    mysql_query("INSERT INTO answers (question_id, answer, correct) VALUES ('$lastId', '$answer1', '0')")or die(mysql_error());
    mysql_query("INSERT INTO answers (question_id, answer, correct) VALUES ('$lastId', '$answer3', '0')")or die(mysql_error());
    mysql_query("INSERT INTO answers (question_id, answer, correct) VALUES ('$lastId', '$answer4', '0')")or die(mysql_error());
    $msg = 'You questions has been succesfully added!';
  header('location: addQuestions.php?msg='.$msg.'');
exit();
}
if($isCorrect == "answer3"){
    $sql2 = mysql_query("INSERT INTO answers (question_id, answer, correct) VALUES ('$lastId', '$answer3', '1')")or die(mysql_error());
    mysql_query("INSERT INTO answers (question_id, answer, correct) VALUES ('$lastId', '$answer1', '0')")or die(mysql_error());
    mysql_query("INSERT INTO answers (question_id, answer, correct) VALUES ('$lastId', '$answer2', '0')")or die(mysql_error());
    mysql_query("INSERT INTO answers (question_id, answer, correct) VALUES ('$lastId', '$answer4', '0')")or die(mysql_error());
    $msg = 'You questions has been succesfully added!';
  header('location: addQuestions.php?msg='.$msg.'');
exit();
}
if($isCorrect == "answer4"){
    $sql2 = mysql_query("INSERT INTO answers (question_id, answer, correct) VALUES ('$lastId', '$answer4', '1')")or die(mysql_error());
    mysql_query("INSERT INTO answers (question_id, answer, correct) VALUES ('$lastId', '$answer1', '0')")or die(mysql_error());
    mysql_query("INSERT INTO answers (question_id, answer, correct) VALUES ('$lastId', '$answer2', '0')")or die(mysql_error());
    mysql_query("INSERT INTO answers (question_id, answer, correct) VALUES ('$lastId', '$answer3', '0')")or die(mysql_error());
    $msg = 'You questions has been succesfully added!';
  header('location: addQuestions.php?msg='.$msg.'');
exit();
    }
}

when i run these, only one gets accepted as the correct answer.

Is there any way for my database to accept two correct answers? I am using enum (0,1) to check for the correct answers.

post your table structure and explain what you want to do - its not clear atm — amdixon, Dec 06 '15 at 04:17
Just like @amdixon said, it would be best if you post your table structure, some sample data and expected output. Also include what you have tried so far. — zedfoxus, Dec 06 '15 at 04:19

score 0 · Answer 1 · edited May 23 '17 at 12:04

Someone will probably reply later with a better answer, this is just to get the ball rolling.

I would start by refactoring what you currently have to something like the following:

// note: there are a lot of ways to make the following cleaner (use an array, 
//   write a function, etc.), but for the sake of time and simplicity I'll
//   leave it at this 

// if $isCorrect == "answer1", $answer1Correct = '1', else $answer1Correct = '0'
$answer1Correct = ($isCorrect == "answer1" ? '1' : '0');
$answer2Correct = ($isCorrect == "answer2" ? '1' : '0');
$answer3Correct = ($isCorrect == "answer3" ? '1' : '0');
$answer4Correct = ($isCorrect == "answer4" ? '1' : '0');

mysql_query("INSERT INTO answers (question_id, answer, correct) VALUES ('$lastId', '$answer1', $answer1Correct)")or die(mysql_error());
mysql_query("INSERT INTO answers (question_id, answer, correct) VALUES ('$lastId', '$answer2', $answer2Correct)")or die(mysql_error());
mysql_query("INSERT INTO answers (question_id, answer, correct) VALUES ('$lastId', '$answer3', $answer3Correct)")or die(mysql_error());
mysql_query("INSERT INTO answers (question_id, answer, correct) VALUES ('$lastId', '$answer4', $answer4Correct)")or die(mysql_error());
$msg = 'You questions has been succesfully added!';
header('location: addQuestions.php?msg='.$msg.'');
exit();

I don't know what you were using $sql2 for, I've ignored it here.

so far, this should do the same as what you currently have

Then, to make the change you're looking for, I would change $isCorrect from string to an array of strings.

For example, suppose answer 1 and 4 are both correct: $isCorrect = array('answer1', 'answer4');

Then you use in_array() so your $answer1Correct section becomes:

// if "answer1" is in the $isCorrect array, set $answer1Correct to '1', otherwise set it to '0'
$answer1Correct = (in_array("answer1", $isCorrect) ? '1' : '0');
$answer2Correct = (in_array("answer2", $isCorrect) ? '1' : '0');
$answer3Correct = (in_array("answer3", $isCorrect) ? '1' : '0');
$answer4Correct = (in_array("answer4", $isCorrect) ? '1' : '0');

Edit: more improvements

instead of using the ternary operator (condition ? true_value : false_value), you can convert a boolean to an integer, then convert that to a string.

(int)true returns the integer 1, and (string)(int)true returns the string '1'.

So your $answer1Correct section becomes: $answer1Correct = (string)(int)in_array("answer1", $isCorrect); $answer2Correct = (string)(int)in_array("answer2", $isCorrect); $answer3Correct = (string)(int)in_array("answer3", $isCorrect); $answer4Correct = (string)(int)in_array("answer4", $isCorrect);

But all of this is a bit tedious to copy+paste so much, a more dry option is to replace $answer1Correct with a function:

function answerIsCorrect($answerNumber, $allCorrectAnswers) {
  return (string)(int)in_array("answer" . $answerNumber, $allCorrectAnswers);
}

And then your queries become:

mysql_query("INSERT INTO answers (question_id, answer, correct) VALUES ('$lastId', '$answer1', answerIsCorrect(1, $isCorrect)")or die(mysql_error());

And now you can do the whole thing as a loop, so your entire script can be shortened to:

function answerIsCorrect($answerNumber, $allCorrectAnswers) {
  return (string)(int)in_array("answer" . $answerNumber, $allCorrectAnswers);
} 

$answers = array(
  1 => $answer1,
  2 => $answer2,
  3 => $answer3,
  4 => $answer4
)

for($i = 1; $i <= 4; $i++) {
  mysql_query("INSERT INTO answers (question_id, answer, correct) VALUES ('$lastId', $answers[$i], answerIsCorrect($i, $isCorrect)")or die(mysql_error());
}

if you change $isCorrect from a string to an array of strings, as above.

how to accept two correct answers in MySQL query?

1 Answers1