下面是我的数据库模式:
user
*user_id
*username
*password
*etc
quiz_response
*response_id
*user_id
*question_id
*response
*is_correct
*answer_time
question_choice
*choice_id
*question_id
*is_correct
*choice (VARCHAR)
question
*question_id
*quiz_id
*question (VARCHAR)
quiz
*quiz_id
*title (VARCHAR)
我正在用PHP建立一个测验web应用程序，我有麻烦。目前，我正在尝试-没有运气-这个查询，我知道问题在哪里，我只是不知道如何解决它。这就是为什么我在S.O.上。
// Grab the response data from the database to generate the form
$query = "SELECT qr.response_id AS r_id, qr.question_id, qr.response, q.question, quiz.title " .
"FROM quiz_response AS qr " .
"INNER JOIN question AS q USING (question_id) " .
"INNER JOIN quiz USING (quiz_id) " .
"WHERE qr.user_id = '" . $_SESSION['user_id'] . "'";
$data = mysqli_query($dbc, $query) or die("MySQL error: " . mysqli_error($dbc) . "<hr>'nQuery: $query");
在这一点上，我觉得我的第二个内部连接(Inner Join quiz USING (quiz_id))是问题。当我不包括这一行，并删除测验。标题从查询它工作。所以，我的问题是我如何维护一个原子数据库模式，同时仍然从表"question"中抓取基于quiz_id的测验标题?任何帮助都将非常感激!
我认为问题是quiz_id不在quiz_response中。我使用ON关键字。试一试:
// Grab the response data from the database to generate the form
$query = "SELECT qr.response_id AS r_id, qr.question_id, qr.response, q.question,
quiz.title " .
"FROM quiz_response AS qr " .
"INNER JOIN question AS q ON (q.question_id = qr.question_id) " .
"INNER JOIN quiz ON (quiz.quiz_id = q.quiz_id) " .
"WHERE qr.user_id = '" . $_SESSION['user_id'] . "'";
$data = mysqli_query($dbc, $query) or
die("MySQL error: " . mysqli_error($dbc) . "<hr>'nQuery: $query");
Try with:
// Grab the response data from the database to generate the form
$query = "SELECT qr.response_id AS r_id, qr.question_id, qr.response, q.question, quiz.title " .
"FROM quiz_response qr " .
"INNER JOIN question q USING (question_id) " .
"INNER JOIN quiz ON quiz.quiz_id = q.quiz_id " .
"WHERE qr.user_id = '" . $_SESSION['user_id'] . "'";
$data = mysqli_query($dbc, $query) or die("MySQL error: " . mysqli_error($dbc) . "<hr>'nQuery: $query");