Using COUNT in a MYSQL Statement

Question

Users can do posts and this posts can have comments and i want to get the number of comments made in all this users posts.

POSTS TABLE:

COMMENTS TABLE:

$stmt = $mysqli->prepare("SELECT COUNT(comments.id) FROM comments INNER JOIN posts ON     
posts.id=comments.users_id WHERE posts.user_id=? ");

$stmt->bind_param('i',$_SESSION['ID']);
$stmt->execute();
$stmt->bind_result($comentarios);
$stmt->fetch();
$_SESSION['comentarios']=$comentarios;

Maybe because you're joining? Read this: codinghorror.com/blog/2007/10/… — fancyPants
– fancyPants, Commented May 19, 2013 at 21:47
Im joining so i can associate the number number of comments to all the posts that user made. I tagged it with PDO because im using mysqli object oriented functions. — Christopher Costa
– Christopher Costa, Commented May 20, 2013 at 12:55

user4035 · Accepted Answer · 2013-05-19 22:34:03Z

4

I suppose, that you have post_id foreign key in comments table.

If you want to get the comments, made on this users posts, try this:

SELECT
    count(comments.id)
FROM
    comments
INNER JOIN
    `posts`
ON
    comments.post_id=posts.id
AND
    `posts`.user_id=?

edited May 19, 2013 at 22:34

answered May 19, 2013 at 21:48

user4035

24k11 gold badges69 silver badges101 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

tadman Over a year ago

This seems like the most direct method as the JOIN is not necessary to do the count.

Christopher Costa Over a year ago

I may be not explicit in my question, but i want all the comments made on this users posts

user4035 Over a year ago

@ChristopherCosta Then improve your question plz: show the relevant tables data and structure and say, what do you want to get.

user4035 Over a year ago

@ChristopherCosta Please, show the tables structure. It's hard to do what you need, without knowing it.

Christopher Costa Over a year ago

@user4035 are it more explicit ?

Collectives™ on Stack Overflow

Using COUNT in a MYSQL Statement

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related