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

user_11247523 0
Viewed 433

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 comments table

Posts table posts table

Users 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.

example usage

join mysql php
2 Answers

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.

user_2833660

For the first part of the problem where you populate each page, you're retrieved ID from POSTS and ID from COMMENTS by using a "SELECT * FROM" over an inner join containing both tables. In that context, columns from POSTS and COMMENTS with the same name (id, img) will be returned and the result will be ambiguous, i.e. in your PHP code when you ask for $row->id, will it be POSTS.ID or COMMENTS.ID?

You need COMMENTS.ID to update the status in COMMENTS. You could just rename "COMMENTS.ID as COMMENT_ID", or select only from COMMENTS knowing the "ID" you get back is going to be COMMENT.ID

Since you only want rows from COMMENTS in the result, I would only select from COMMENTS and use a subquery to constrain it by POSTS instead of a join, and also further limiting the SELECT to the list of columns you explicitly need for your result so you don't have to pay for returning columns you don't need, e.g.

SELECT C.ID, C.NAME, C.CONTENT, C.VOTES C.STATUS /* but you know that STATUS already, heheh */
FROM COMMENTS C WHERE 
  C.STATUS = :STATUS AND
  C.PAGE_ID IN ( SELECT P.ID FROM POSTS P WHERE P.USER_ID = :USER )

Helpful indices:

  • COMMENTS on STATUS, PAGE_ID
  • POSTS on USER_ID

Assumptions:

  • COMMENTS.PAGE_ID --> POSTS.ID Foreign key dependency.
  • POSTS.ID and COMMENTS.ID are unique.
  • You're not going to get back so many rows that you need to paginate...a different approach is needed for a limit query.
user_141267100
Related