PHP MySQL – get comment count for all posts written by one user

My aim is to get a total count of ‘pending’ and ‘approved’ comments for all posts for a specific user. The comments table has a status column for ‘pending’ or ‘approved’. For the record, the page_id in the comments table maps to the id in the posts table.

I started writing the statement below – but I think I have it back to front:

 $sql = 'SELECT * FROM posts JOIN comments on posts.id = comments.page_id WHERE author = posts.author';
 $stmt = $pdo->prepare($sql);
 $stmt->execute();
 $results = $stmt->fetch();

To give visibility to the table structures, below are the table columns.

Comments table

Posts table

Users table

I have a foreign key the relates the users id to the posts user_id.

I’m going to continue to try to do this – but – would like some guidance on how to achieve make this work.

Edit:

I have an admin panel for users. I want to create a table of approved comments with the approved comment count at the top, and a table of pending comments with the pending comment count at the top. In each table will be a button to either approve or unapprove comments – but only for their posts. Here’s an image of how I have done this for all comments.

Answer

Seems a pretty straight-forward query using SUM and CASE

SELECT
  COALESCE(SUM(CASE WHEN c.status = 'approved' THEN 1 ELSE 0 END), 0) AS approvedCount,
  COALESCE(SUM(CASE WHEN c.status = 'pending' THEN 1 ELSE 0 END), 0) AS pendingCount
FROM comments c
INNER JOIN posts p
  ON c.page_id = p.id
WHERE p.user_id = :user_id

The main thing this query does is adds up all the comments rows with status = 'approved' and another where status = 'pending' as two separate counts.

The COALESCE functions are there to cater for when there are no matching records for the user_id you’re looking for. If they were not used, the result would be null instead of 0.