mysql - 如何在MySQL查询中接受两个正确答案？

Question

我有四个选择，其中两个都可以是正确答案。

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();
    }
}

当我运行这些时，只有一个被接受为正确答案。

有什么办法让我的数据库接受两个正确答案吗？我正在使用 enum (0,1) 来检查正确答案。

Answer 1

稍后可能会有人回复并提供更好的答案，这只是为了让事情顺利进行。

我首先将您当前拥有的内容重构为如下所示:

// 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();

我不知道您使用 $sql2 做什么，我在这里忽略了它。

到目前为止，这应该与您当前所做的相同

然后，为了进行您想要的更改，我会将 $isCorrect 从字符串更改为字符串数组。

例如，假设答案 1 和 4 都正确: $isCorrect = array('answer1', 'answer4');

然后你使用in_array()因此您的 $answer1Correct 部分将变为:

// 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');

<小时/>

编辑:更多改进

您可以 convert a boolean to an integer 而不是使用三元运算符(条件？ true_value : false_value) ，然后将其转换为字符串。

(int)true 返回整数 1，(string)(int)true 返回字符串“1”。

因此您的 $answer1Correct 部分将变为: $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);

但是所有这些复制+粘贴这么多有点乏味，更多dry选项是将 $answer1Correct 替换为函数:

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

然后你的查询就变成了:

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

现在您可以将整个事情作为一个循环来完成，因此您的整个脚本可以缩短为:

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());
}

如果将 $isCorrect 从字符串更改为字符串数组，如上所述。