目前我有三个表,分别是users
、topics
和comments
,注释表有两种不同类型的注释cat_comments
和dog_comments
。我想实现一个新闻源,我将在其中显示主题或评论中的每个新提交。诀窍是我使用 smarty
作为模板引擎,如果我想按新主题开始日期或提交评论的日期排序,则只能执行一个foreach
循环。
我尝试这样做,但它多次返回相同的行:
$stmt = $db->prepare('SELECT title, username, cat_comments, dog_comments
FROM topics,
users, topics, comments WHERE topics.user_id = users.id OR comments.user_id =
users.id ORDER BY
topics.date_started OR comments.date_posted DESC');
我的表的结构:
users:
- id
- username
topics:
-topic_id
-title
-user_id (the user that started this topic)
-date_started
comments:
-id
-user_id (user that posted the comment)
-cat_comments
-dog_comments
-date_posted
所以我想以我可以显示这样每条新闻的方式加入它:
Username has started topic (title of topic)
date_started
如果接近这个时间有人发表评论,我想显示:
Username left a cat_comment on topic (title of topic)
date_posted
或
Username left a dog_comment on topic (title of topic)
date_posted
任何建议或帮助都会非常高兴!提前感谢大家!
可能是你需要一个独特的连接
SELECT distinct t.title, u.username, c.cat_comments, c.dog_comments
FROM user u
left join topics t on t.user_id = u.id
left join comments c on c.user_id = u.id
ORDER BY t.date_started DESC, c.date_posted DESC