用PHP连接查询MySQL数据库失败

Here is my database schema:

下面是我的数据库模式:

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)

I am building a quiz web-app using PHP and I am having trouble. Currently, I am trying -- with no luck -- this query and I know where the problem is, I just don't know how to solve it. Hence, why I am here on S.O.

我正在用PHP开发一个测试web应用程序，我遇到了麻烦。目前，我正在尝试——没有运气——这个查询，我知道问题在哪里，我只是不知道如何解决它。所以，我来这里是为了

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

At this point I feel my second Inner Join (INNER JOIN quiz USING (quiz_id)) is the problem. When I don't include this line and remove the quiz.title from the query it works. So, my question is how do I maintain an atomic database schema while still grabbing the quiz title based on the quiz_id from the table 'question'? Any help would be much appreciated!

此时，我觉得第二个内部连接(使用quiz_id进行内部连接测试)是问题所在。当我不包括这一行并删除测试。它工作的查询的标题。那么，我的问题是，如何在保持一个原子数据库模式的同时仍然根据表'question'中的quiz_id获取测试标题?如有任何帮助，我们将不胜感激!

2 个解决方案

#1

I believe that the problem is that quiz_id is not in quiz_response. I use the ON keyword. Try:

我认为问题在于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");

#2

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

#1