Number of rows

Databases
1

hello

ive got two rows

post( post_id, user_id, post)
comment (comment_id, post_id, comment)

I’ve got a loop setup to echo each post in my php.

How do I echo the number of comments that each post have?
I’ve already setup a join which connects the comment to the post.

2

those look like tables, not rows

can i see it?

3

Hi, yes sorry I meant tables.

Here is the join


LEFT JOIN comments
ON
dbPosts.post_id = comments.comment_post_id

here is the whole query


"SELECT dbUsers.*, dbPosts.*, dbKarma.*, comments.*

FROM dbPosts 
LEFT JOIN dbUsers
ON dbPosts.username_id = dbUsers.id


LEFT JOIN dbKarma
ON (dbKarma.karma_post_id = dbPosts.post_id)
AND
(dbKarma.karma_user_id = $user)

LEFT JOIN dbPosts_share
ON (dbPosts.post_id = dbPosts_share.share_post_id)
OR (dbPosts.username_id = dbPosts_share.share_user_id)

LEFT JOIN dbFriends
ON
dbPosts.username_id = dbFriends.my_id
OR 
dbPosts.username_id = dbFriends.friend_id
OR
dbPosts_share.share_user_id = dbFriends.friend_id
OR 
dbPosts_share.share_user_id = dbFriends.my_id

LEFT JOIN comments
ON
dbPosts.post_id = comments.comment_post_id

WHERE
(dbFriends.my_id = $user)
OR
(dbFriends.friend_id = $user)
AND
(dbFriends.status = 1)
OR
dbPosts.username_id = $user


GROUP BY
    dbUsers.username
  , dbUsers.id
  , dbPosts.post_id
  , dbPosts.username_id
  
  ";
4

it appears that you have a GROUP BY that is not really required

plus, you’re still using the dreaded, evil “select star”

and then there is the complexity of all those ORs that drive me nuts, both in the join but especially in the WHERE clause, where the ORs are mixed with ANDs…

sigh

in any case, you should remove the GROUP BY, and replace the join to the comments table with this –

LEFT OUTER
  JOIN ( SELECT comment_post_id
              , COUNT(*) AS num_comments
           FROM comments
         GROUP
             BY comment_post_id ) AS sq
    ON sq.comment_post_id = dbPosts.post_id

and then replace the dreaded comments.* in the SELECT clause with sq.num_comments

5

Thanks!!! works perfectly.

working on those dreaded ORs and *'s