COUNT DISTINCT row count in MySQL

Question

I am creating a comment system with PHP / MySQL. This comment system has a nice feature that allows users to select text (saved as "selected_text" in the database), and the selected text will be saved in the database with the user's comment. In addition, I also save the paragraph (from which the selected text was selected) as INT. These values ​​are stored correctly in the database, but now I want to do something with them.

I want to create "comment counters". These comment counters will be placed next to each paragraph in the article, and they will display the total number of comments that were made in this paragraph, they are attached. Visual overview of "comments": table structure:

My last request to try to get this information:

$distinct = mysql_query("SELECT COUNT(DISTINCT paragraph_id) FROM comments");

And the corresponding PHP code:

 while ($result_three = mysql_fetch_array($distinct)) { echo $result_three['paragraph_id']; } 

Now I think that maybe I'm wrong. I considered trying to run a query that first finds all DISTINCT paragraph_ids . After that, I would run for each loop that counts the number of times that those paragraph_ids appear.

Any help would be greatly appreciated, as the request I'm working on right now does not seem to achieve my goal. In addition, I am concerned that for me there is no clear way to clearly link the calculated data to the “comment account”.

Answer 1

Basically, you need to count comments in each group of paragraph identifiers. The correct request is below:

 $distinct = mysql_query("SELECT paragraph_id, (DISTINCT comment_id) as comment_count FROM comments GROUP BY paragraph_id"); while ($result_three = mysql_fetch_array($distinct)) { echo $result_three['paragraph_id']; // Gives the id of the paragraph echo $result_three['comment_count']; // Gives the comment count for the associated paragraph } 

Answer 2

If I understand your problem correctly, you need GROUP BY paragraph_id . Try the following:

 SELECT paragraph_id, COUNT(*) FROM comments GROUP BY paragraph_id 

Answer 3

Most likely you want to know when there are null comments, you need to make an external connection with paragraphs.

 SELECT p.paragraph_id, COUNT(c.comment_id) as comment_count FROM comments c RIGHT JOIN paragraphs p ON c.paragraph_id = p.paragraph_id GROUP BY p.paragraph_id 

sample data.se works here

Answer 4

Use below code, this should work

 $res = mysql_query("SELECT COUNT(DISTINCT paragraph_id) as cnt FROM comments"); while ($result_three = mysql_fetch_assoc($res)) { echo $result_three['cnt']; }