在PHP查询中难以连接3个表 [英] Difficulty with join 3 tables in a query in php
问题描述
我的数据库有3个表,我希望在选择查询中访问这些表,但似乎无法正常工作.从2个表中进行选择可以很好地工作,因此我知道除了从3个表中进行选择的代码之外,其他所有功能都在起作用.我的数据库已在PHPmyadmin上创建
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
表如下:
forum_replies
forum_replies
- reply_id
- topic_id
- user_id
- reply_text
- 回复日期
forum_topics
forum_topics
- topic_id
- category_id
- user_id
- topic_title
- topic_description
- topic_date
用户
- user_id
- 用户名
这是我尝试使用的代码,并显示了我希望选择的字段:
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);
代码示例.谢谢
推荐答案
使用此查询:
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
除语法错误外,您还应使用LEFT JOIN
和表别名.
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
这篇关于在PHP查询中难以连接3个表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!