How to count all comments?

[Masterslave]

Hello all,

MySQL Table articles:
ID
title
category
keywords
description
author
comment
content
thumb
timestamp
favorite

MySQL Table comments:
ID
ID_article
name
content
timestamp
ip
host


$requestCountComments =
"SELECT COUNT(ID_article)
FROM $commenttable
WHERE ID_article= '" . mysql_real_escape_string($_GET['ID']) . "'";
$getCountComments = mysql_query($requestCountComments) or die(mysql_error());

<li><a href="article.php?ID=<?=mysql_result($properArticle, 0, 0);?>#comments" title="Er zijn <?=mysql_result($getCountComments, 0 , 0);?> reacties op dit artikel"><?=mysql_result($getCountComments, 0 , 0);?> reacties</a></li>

That is the code when I'm viewing one article.

On the homepage I've 20 articles, how can I show how much comments there is for each article.
I have to change something about my WHERE clause but I don't know what.

[mio]

hi,

replace:
count(id_article)
by:
count(*)

[guelphdad]

That doesn't work without a group by clause now does it?


select
id_article,
count(*)
from yourtablename
group by
id_article

[Masterslave]

That doesn't work without a group by clause now does it?


select
id_article,
count(*)
from yourtablename
group by
id_article

Mmmm, it doesn't work quite right. Now PHP/MySQL says that article one and two had 1 comment. Article 1 has indeed one comment but article 2 has 3 comments. I've used your code guelphdad. What is going wrong and also, what does the GROUP BY clause do?

[guelphdad]

can you show some actual rows from your table? Insert statements would help so I or someone else can test them for you.

THe group by clause says to group the id_articles together by the same number or name and then count.

If you have
id_articles of:
5, 6, 7, 7, 8, 8, 8, 8
and don't use the group by clause you will get a count of 1 for each of those. with the group by clause you would get
5: 1
6: 1
7: 2
8: 4

[Masterslave]

and don't use the group by clause you will get a count of 1 for each of those.
Correct.

I've solved it.
Yesterday I've cleaned up my files and structure it again.
I've two functions:

function getArticleProperties()
{
$requestAllArticleProperties = "
SELECT *
FROM " . ARTICLE_TABLE . "
ORDER BY ID DESC";
$allArticleProperties = mysql_query($requestAllArticleProperties) or die(mysql_error());

$resultAllArticleProperties = array();
while($row = mysql_fetch_assoc($allArticleProperties))
{
$resultAllArticleProperties[] = array
(
'ID' => $row['ID'],
'TITLE' => $row['title'],
'CATEGORY' => $row['category'],
'KEYWORDS' => $row['keywords'],
'DESCRIPTION' => $row['description'],
'AUTHOR' => $row['author'],
'COMMENT' => $row['comment'],
'CONTENT' => $row['content'],
'THUMB' => $row['thumb'],
'TIMESTAMP' => $row['timestamp'] ,
'FAVORITE' => $row['favorite'],
'COMMENT_COUNT' => getArticleCommentCount($row['ID'])
);
}
return $resultAllArticleProperties;
}

function getArticleCommentCount($articleID)
{
$requestCountComments ="
SELECT COUNT(ID_article)
FROM " . COMMENTS_TABLE . "
WHERE ID_article= '" . mysql_real_escape_string($articleID) . "'";
$countCommentsResult = mysql_query($requestCountComments) or die(mysql_error());
return mysql_result($countCommentsResult, 0);
}
With this XHTML/PHP:

$articleProperties = getArticleProperties();
foreach ( $articleProperties as $propertie )
{ ?>
<li><a href="article.php?ID=<?=$propertie['ID'];?>#comments" title="Er zijn <?=$propertie['COMMENT_COUNT']?> <?=$propertie['COMMENT_COUNT'] == 1 ? "reactie" : "reacties"?> op dit artikel"><?=$propertie['COMMENT_COUNT'];?></a></li>
<?}?>
A friend helped me out with the COUNT issue.

But thanks for your help guelphdad.

[guelphdad]

Your query is more complicated than it needs to be and thus slower and more resource intensive. any time you do a loop in php instead of getting your result directly from your database query you are wasting resources.

you also changed what you were asking for from your original question to your proposed solution. Kind of hard to help when we don't know what you are asking for.

[Masterslave]

Your query is more complicated than it needs to be and thus slower and more resource intensive. any time you do a loop in php instead of getting your result directly from your database query you are wasting resources.
Why is my query complicated? I don't understand. I put the results in an array for debugging. When I dump the array I can exactly see what the value are.

you also changed what you were asking for from your original question to your proposed solution. Kind of hard to help when we don't know what you are asking for.
I asked for showing the result of the amount of comments per article. And that's what I'm accomplished.
Look at the screenshot below and look at the "2" and "3" that's the number of comments from that article:
http://www.members.lycos.nl/masterslave1/comments.jpg

[guelphdad]

There isn't a problem with you usiing the array. You are selecting all rows from your article_table without joining it to your comments table. you are then doing an additional query to your comments table to count the number of comments. You are doing that second query while you are parsing through the results of your first table. you are thus doing an unnecessary query to get that count.

you also said in your initial query that you wanted the count of the number of articles, which is calculated alone from the comments table, yet you have returned all the columns from your article table. so you have provided a solution for a question you didn't ask. you then made it more resource intensive than it needs to be.

[Masterslave]

All right I get it. I shall look to my queries to optimize it. Thanks for the reply.:thumbsup: Your a good dude.

[guelphdad]

Just trying to point out that while something may work now, as it scales it may slow your site down beyond what is acceptable. :)