CODEWITHSUNDEEP
phpmysql
Eric_ph
Eric_ph

Reputation: 128

PHP: count the items and echo them

I have three tables: table 1: topics table

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

table 2: replies table

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

table 3: comments table

comment_id    topic_id      message
50            1             bla bla
51            2             bla bla

As you can see, the table 2 and 3 are related to the table 1 via the foreign key called topic_id

I use the following query to SELECT and ECHO the data as follows:

$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']};
   }
}

As you can see, there are two replies and one comment for the topic_id number 1

However, it outputs two replies and two comment as below:

name       replies     comments
blue        2            2

The correct result should be 2 replies and 1 comments.

Can you help me, pls?

Thanks

Upvotes: 0

Views: 116

Answers (2)

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44844

Does it helps 

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

check the demo here http://www.sqlfiddle.com/#!2/1412f/7

Upvotes: 1

Anthony Raymond
Anthony Raymond

Reputation: 7862

You may try 

SELECT name,
  COUNT(DISTINCT(replies.reply_id)) AS replies,
  COUNT(DISTINCT(comments.comment_id)) AS comments
 FROM topics
   INNER JOIN replies
     ON topics.topic_id = replies.topic_id
   INNER JOIN comments
     ON topics.topic_id = comments.topic_id

Upvotes: 0

Related Questions