使用表连接选择特定数据

Question

I am attempting to output some a user username from the user table by joining it from a questions table, the intention being I can show which user posted this specific question.

users with id, username

discussion_q id, question_text, user_id

Here is where I am at:

$sql = "SELECT q.id AS questionId, q.question_text AS questionText, q.user_id AS questionUserId, q.published AS questionPub, users.id AS userId 
                    FROM discussion_q 
                    JOIN users
                    ON questionUserId = userId
                    WHERE project_id = '$projectId'
                    ORDER BY published";

I am getting 0 results returned back to me of course. I am sure I have over engineered this or missed something simple?

Here is my php to return the results:

    $result = $conn->query($sql);
    if (mysqli_num_rows($result) > 0) {
        while($row = mysqli_fetch_assoc($result)) {
            echo '<div class="twelve columns">
                  <p><a href=".php?project_id=' . $row['id'] .'">' . $row['question_text'] . '</a></p>
                  <p>' . $row['published'] . ' by ' . $row['username'] . '</p>
                  </div>';
        }
    } else {
        echo "0 results";
    }

So the end goal is to output the question_text with the username of the user who posted.

Answer 1

$sql = "SELECT q.id AS questionId, q.question_text AS questionText, q.user_id AS questionUserId, q.published AS questionPub, users.id AS userId 
                    FROM discussion AS q 
                    JOIN users
                    ON (q.user_id = users.id)
                    WHERE project_id = '$projectId'
                    ORDER BY published";