CODEWITHSUNDEEP
phpmysqlcountmysqli
Christopher Costa
Christopher Costa

Reputation: 17

Using COUNT in a MYSQL Statement

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:

ID | CONTENT | FOTO | likes | genious | USER_ID | TIMEPOST | YOUVIDEO |

COMMENTS TABLE:

ID | USERS_ID | POSTS_ID | CONTENT | TIMECOMMENT |

$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;

Upvotes: 0

Views: 121

Answers (1)

user4035
user4035

Reputation: 23729

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=?

Upvotes: 4

Related Questions