CODEWITHSUNDEEP
phpmysql
S.I.
S.I.

Reputation: 3375

Select from two tables and count how many comments has post

I'm trying to make query which will show me the post and the number of comments that have this post. This is the query but I got blank page

$result = $pdo->query("
    SELECT posts.*, comments.count(*)
    FROM posts
    LEFT JOIN comments ON posts.post_id = comments.post_id
    WHERE ( comments.post_id
          IN ( 
                SELECT MAX( post_id ) 
                FROM comments
                GROUP BY post_id
          )
    )
    OR ( 
          NOT EXISTS 
          (
                SELECT NULL 
                FROM comments
                WHERE comments.post_id = posts.post_id
          )
    )
    AND posts.post_id = :post_id
"); 

if ($result->execute(array(':post_id'=>$_GET['post_id'])))
{
      // rest of the code
}

I'm not even sure that the query is proper constructed. The goal is to show the post and count number of comments. Like This post have 3 comments

When I execute this code I get the following error:

Fatal error: Call to a member function execute() on a non-object

which occurs on this line:

if ($result->execute(array(':post_id'=>$_GET['post_id'])))

Update: Final and work form

SELECT posts . * , COUNT( comments.comment_id ) AS total
FROM posts, comments
WHERE  posts.post_id = comments.post_id
AND posts.post_id=:post_id

Upvotes: 1

Views: 53

Answers (1)

Pankaj katiyar
Pankaj katiyar

Reputation: 464

try with query 

SELECT posts.*,COUNT(comments.`post_id`) AS totalcomment
FROM posts
LEFT JOIN comments   ON posts.post_id = comments.post_id

Upvotes: 1

Related Questions