How to display the number of comments on an article?

Question

PHP

There are two tables in the database.
1st table with articles (articles) 2nd table with
comments on these articles It is

necessary to display the number of comments for each article, and for those articles where there are no comments, indicate 0
I display all this on the main page of the site. Right here (indicated by the red arrow)
.
How can I implement this correctly? How to form the correct query to the database, so that later all this can be easily displayed using the foreach loop??

Answer 1

You can use a subquery to select the number of comments immediately when selecting an article.

select a.id, a.title, a.content,
       (select count(*) from comments where article_id=a.id) as comments_count
    from articles as a

Answer 2

SELECT count(*) from articles

Answer 3

SELECT 
COUNT(comment_id)
FROM articles
LEFT JOIN comments
ON comments.article_id = articles.id
GROUP BY articles.id;

This only outputs the count. Add whatever else needs to be output to SELECT.