php 数据库查询join,使用JOIN未能成功通过PHP查询MySQL数据库

这是我的数据库架构：

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) . "

---

\nQuery: $query");

此时,我感觉到我的第二个内部联接(INNER JOIN测验USING(quiz_id))是问题所在.当我不包括这一行并从查询中删除quiz.title时,它将起作用.因此,我的问题是如何在仍然基于“问题”表中的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) . "

---

\nQuery: $query");

标签：sql,mysql,php,database

来源： https://codeday.me/bug/20191102/1989112.html