[PHP & MySQL] Blog Comments, COUNT() comments?

[four1seven]

Hey all, I'm working on a commenting code now, and I've got the comments working great it's just that I wanted to start putting the number of comments on the actual blogs. You know, how MANY comments are within each blog. You know how most blogs look - "Comments (8)" say if there is 8 comments.

I have it "working", but it only works on entries that actually have comments, it doesn't work for entries that have 0 comments, I am assuming because COUNT() only works on Non Null values.

well here is my page - http://www.joelsmith.name (http://www.joelsmith.name/)

the blogs with commets are the first one - Commenting code, and the third I belive, Rendition, or something like that.

Here is the code I am using, any help would be greatly appreciated. Thanks!


<?php
include_once('inc/mysqlconnect.inc');
@mysql_select_db("database");
$id = isset($_GET['recent']) ? $_GET['recent'] : $_GET['id'];

$sql = "SELECT DATE_FORMAT(date, '%b. %D, %Y - %r') AS the_date, COUNT(c_blog_id) AS c_total, entryNo, title, author, entry, category FROM
blog_entry, blog_comments WHERE entryNo = '$id' AND c_blog_id = '$id' GROUP BY (title)";

$result = mysql_query("SELECT DATE_FORMAT(date, '%b. %D, %Y - %r') AS the_date, COUNT(c_blog_id) AS c_total, entryNo, title, author, entry, category FROM
blog_entry, blog_comments WHERE entryNo = '$id' AND c_blog_id = '$id' GROUP BY (title)") or die(mysql_error());

if(mysql_num_rows($result)== 0)
{

echo "<div class=\"tableBlog\"><center><b><font size=\"3\">Sorry this entry is not found, please try another. Thanks.</font></b></center></div>";

}else{

while($row = mysql_fetch_array($result))
{

echo "<div class=\"tableBlog\">";
echo "<b><div align=\"right\"><font color=\"#FFFFFF\">$row[the_date]</font></b></div>";
echo "<hr color=\"#ffffff\">";
echo "<br />";
echo "<br /><div class=\"entryHeader\">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<b><font family=\"Trebuchet MS\" size=\"3\" color=\"#FFFFFF\">$row[title]</font></b></div><br /><br />";
echo "<br />";
echo "<br /><font color=\"#FFFAFA\">$row[entry]</font><br />";
echo "<br />";
echo "<br /><br /><hr color=\"#ffffff\">";
echo "<font color=\"#FFFFFF\">Posted by: $row[author] on $row[the_date] | <a href=\"comment.php?comment=$id\">Comments($row[c_total])</a> | $row[category]</font>";
echo "</div>";
echo "<div class=\"copyright2\" align=\"right\"><a href=\"#top\">^ top</a></div>";
}
}
?>

[Yeldarb]

How about throwing in a


if(!$row[c_total]) {
$row[c_total] = "0";
}

[four1seven]

where? lol

:puzzled:

[SmoothDime]

you need to join your entry and comments tables with a 'left join'. a 'left join' is a special type of join that returns null/0 instead of nothing like a normal join would.

[four1seven]

thanks! i'll check out my SQL book to see if I can get it thanks again

[four1seven]

do both of these tables have to have at least one column name the same to join?

[Yeldarb]

Put that after the query, assuming that variable isn't set, or is NULL, it will just change it to 0.

[four1seven]

So it should look like this? Sorry if that's not right, but if that's how you wanted it put in, it still didn't work. :(

I'm a failure.


$result = mysql_query("SELECT DATE_FORMAT(date, '%b. %D, %Y - %r') AS the_date, COUNT(c_blog_id) AS c_total, entryNo, title, author, entry, category FROM
blog_entry, blog_comments WHERE entryNo = '$id' AND c_blog_id = '$id' GROUP BY (title)") or die(mysql_error());

if(!$row[c_total]) {
$row[c_total] = "0";
}

if(mysql_num_rows($result)== 0)
{

echo "<div class=\"tableBlog\"><center><b><font size=\"3\">Sorry this Entry is not found, please try another. Thanks.</font></b></center></div>";

}else{