Difficulty with join 3 tables in a query in php

问题:

My database has 3 tables i wish to access in the select query but I cannot seem to get it to work. Selecting from 2 tables works fine so I know everything else is working apart from my code for selecting from 3 tables. My database has been created on PHPmyadmin

The Tables are as follows:

forum_replies

• reply_id
• topic_id
• user_id
• reply_text
• reply date

forum_topics

• topic_id
• category_id
• user_id
• topic_title
• topic_description
• topic_date

users

• user_id
• username

This is the code I have tried to use and shows the fields I wish to select:

    $queryreply = "SELECT forum_replies.reply_id, forum_replies.topic_id, forum_replies.user_id,
                       forum_replies.reply_text, forum_replies.reply_date, users.user_id, users.username
                       forum_topics.topic_id,forum_topics.topic_title, forum_topics.topic_date
                       FROM forum_replies
                       JOIN forum_topics
                       ON forum_replies.topic_id = forum_topics.topic_id
                       JOIN users
                       ON forum_replies.user_id = users.user_id

                       ";


        $result = mysql_query($queryreply) or die (mysql_error());
        $row = mysql_fetch_array($result); 

Example in code would be appreciated. Thanks

回答1:

Use this query:

SELECT a.reply_text, a.reply_date, b.topic_title, c.username
FROM forum_replies a
LEFT JOIN forum_topics b ON a.topic_id=b.topic_id
LEFT JOIN users c ON a.user_id=c.user_id
// apply WHERE, ORDER, GROUP if needed

Apart from syntax errors, you should use LEFT JOIN and table alias in your case.

---

To show also the topic creator's username, you can adjust the query to the following:

SELECT a.reply_text, a.reply_date, b.topic_title, c.username AS reply_user, (SELECT username FROM users WHERE user_id=b.user_id) AS topic_creator
FROM forum_replies a
LEFT JOIN forum_topics b ON a.topic_id=b.topic_id
LEFT JOIN users c ON a.user_id=c.user_id
// apply WHERE, ORDER, GROUP if needed

回答2:

You miss the , after users.username..

SELECT forum_replies.reply_id, forum_replies.topic_id, forum_replies.user_id,
forum_replies.reply_text, forum_replies.reply_date, users.user_id, users.username,
 forum_topics.topic_id,forum_topics.topic_title, forum_topics.topic_date