displaying number of comments for a custom blog

[PhraiL]

Hey guys,

 

I've built a custom blog and I added a comment system to it. So far everything works fine, it displays the comments for each article posted. The trouble I am running into I can't seem to wrap my head around.

 

I want to display a comments button like alot of other sites have. IE: comments(3)

 

I can't seem to figure out how to get the number of comments posted. I've played around with the COUNT() feature and I can't get it to post right.

 

Does anyone know of an article/tutorial that is based on something similar or have a solution to this problem?

 

Any input is greatly appreciated, thanks!

[premiso]

Something like this:

 

SELECT count(commentid) as commentCount FROM table_name WHERE parentid = 1;

 

Something like that would do it.

[PhraiL]

I have something similar to that, but now that only displays how many comments there are for article 1 right? How would you go to display the number of comments for each different article?

[premiso]

SELECT count(commentid) as commentCount FROM table_name GROUP BY parentid;

 

Should do it.

[PhraiL]

Gotcha.

 

Another little problem >.<

 

I have my blog posts in a while statement so for every post I have there are comments. When I do the GROUP BY it displays all the comments one after the other like: comments(13) which is how many I have currently in the database which is 4. It pulls two amounts (1 and 3). Now I'm stuck with figuring out how to associate each number of comment to the article.

 

Here's my code:

 

$numComments = mysql_query("SELECT COUNT(comment_id) FROM comments GROUP BY post_id") or die(mysql_error());

 

while(($row = mysql_fetch_array($latestPost))&&($row2 =  mysql_fetch_array($numComments))){
    // echo "<div id=\"spacer\"></div>";
    $postID = $row['post_id'];
    $postTitle = $row['title'];
    $postDate = $row['date'];
    $postMessage = htmlspecialchars($row['description']);
    $postMessage = substr($row['description'],0,400);
    $postMessage = nl2br($postMessage);
    $postMessage = makeClickableLinks($postMessage);
    
    $comrow = $row2['COUNT(comment_id)'];
    
    $thisPosting = "<div id=\"latestPost" ."\">";
    $thisPosting .= "<h1 class=\"grey" ."\">$postTitle</h1>";
    $thisPosting .= "<p>$postMessage...</p>";
    $thisPosting .= "<p class=\"readMore" ."\"><a href=\"/admin/article.php?post_id=$postID\">Read More</a>";
    $thisPosting .= "<p class=\"postDate" ."\">Posted $postDate | <a href=\"/admin/article.php?post_id=$postID#comments\">";
    $thisPosting .= "Comments(";

    while($row2 =  mysql_fetch_array($numComments)){
         $thisPosting .= $row2['COUNT(comment_id)'];
    }

    $thisPosting .= ")";
    $thisPosting .= "</a> | <a href=\"edit-post.php?post_id=$postID" ."\">Edit Article</a></p>";
    $thisPosting .= "";
    $thisPosting .= "</div>";
    
    //$thisPosting .= "<a href=\"edit.php?blog_id=$varID" ."\"><u>Edit</u></a> ";
    
    echo $thisPosting;
}

 

I am pretty sure it's the second while statement that's not quite right, but I'm not too sure how to write it out.

[premiso]

Simple, I am not sure how to do it with one query, but easily done with two:

 

<?php
$numComments = mysql_query("SELECT COUNT(comment_id) as comCount, post_id FROM comments GROUP BY post_id") or die(mysql_error());

while ($row = mysql_fetch_assoc($numComments)) {
    $arQuery = mysql_query("SELECT * FROM articles WHERE post_id = " . $row['post_id']);
    $articles[$row['post_id']] = mysql_fetch_assoc($arQuery);
    $articles[$row['post_id']]['count'] = $row['comCount'];
}

print_r($articles);

 

Then you can just use that array $articles to call what you want.

 

This my also work, but not sure:

<?php
$numComments = mysql_query("SELECT COUNT(comment_id) as comCount, parent.* FROM comments, parents WHERE comments.post_id = parents.post_id GROUP BY post_id") or die(mysql_error());

while ($row = mysql_fetch_assoc($numComments)) {
    $rows[] = $row;
}

print_r($rows);

 

If the latter one works. I would use that cause then you can place order by's etc on it.