How to count the number of comments for each article?

Question

I create a table the put the comments of each article so the comment_id is the auto increment for count the comments and article_id is the article ID from another table (foreign key).

I want to count the number of comments for each article and put it in new record or something just so I can show it in the article page like : "the number of comments : 5"

Answer 1

You can use COUNT and GROUP BY for this

Try:

SELECT COUNT(*) as num_comments, article_id 
FROM comment_table 
GROUP BY article_id

Answer 2

I am just doing this from the top of my head so not sure if it will work:

// once you have connected to the database:
$query = "SELECT COUNT(article_id) FROM table_name WHERE article_id=1";
// you might want to have a variable set so that the article_id isn't hardcoded

$result = mysql_query($query) or die(mysql_error());

// Print out result
while($row = mysql_fetch_array($result)){
echo "the number of comments: ". $row['COUNT(article_id)'] ."
}

Answer 3

  select article_id, count(article_id) as comment_count
    from comments
group by article_id
order by article_id