PHP:计算项目并回显它们

Question

我有三张表:表1:主题表

topic_id       name      date
1              blue         3/20/14
2              green        3/21/14
3              red          3/22/14

表2:回复表

reply_id      topic_id      message 
12            1             bla bla
13            1             bla bla
14            2             bla bla

表3:评论表

comment_id    topic_id      message
50            1             bla bla
51            2             bla bla

如您所见，表 2 和表 3 通过名为 topic_id 的外键与表 1 相关

我使用以下查询来选择和回显数据，如下所示:

$query ="SELECT name, COUNT(replies.topic_id) AS replies, COUNT(comments.topic_id) AS comments
         FROM topics
         LEFT JOIN replies
         ON topics.topic_id = replies.topic_id
         LEFT JOIN comments
         ON topics.topic_id = comments.topic_id";

$r = mysqli_query($dbc, $query);
if (mysqli_num_rows($r) > 0) {
   while ($num = mysqli_fetch_array($r, MYSQLI_ASSOC)) {
      echo {$num['name']};
      echo {$num['replies']};
      echo {$num['comments']};
   }
}

如您所见，topic_id 号 1 有 2 条回复和 1 条评论

但是，它输出两个回复和两个评论，如下所示:

name       replies     comments
blue        2            2

正确的结果应该是 2 条回复和 1 条评论。

请问你能帮我吗？

谢谢

Answer 1

有帮助吗

select 
t.name, 
`replies`,
`comments`
from topics t
left join 
( 
    select topic_id,count(reply_id) as `replies`
    from replies
    group by topic_id
)r
on r.topic_id = t.topic_id
left join
(
    select topic_id,count(comment_id) as `comments`
    from comments
    group by topic_id
)c
on c.topic_id = t.topic_id
group by t.name

在此处查看演示 http://www.sqlfiddle.com/#!2/1412f/7