counting similar rows

cindango

I have a news system, now with commenting. I want to say # Comments where # is the total number of comments that pertain to a certain newsid.

$query1 = "SELECT COUNT( newsid ) FROM comments GROUP BY newsid";
$num = mysql_fetch_object($query1) or die (mysql_error());

but I get this error:

Warning: mysql_fetch_object(): supplied argument is not a valid MySQL result resource in

Any suggestions? I found that code from another post on this forum, and after much searching I haven't found a solution...I'll continue though while I wait for replies..

LordShryku

You need to mysql_query it first.....

cindango

$query1 = mysql_query("SELECT COUNT( newsid ) FROM comments GROUP BY newsid");
$num = count($query1) or die (mysql_error());

like that? sorry I'm pretty new at this..

LordShryku

$sql = "SELECT count(newsid) AS cnt
          FROM comments 
      GROUP BY newsid";
$res = mysql_query($sql) or die(mysql_error());
$num = mysql_fetch_array($res);
echo $num['cnt'];

cindango

Excellent, thanks.

http://cindango.com

but one more question, how do I make it say 0 if there are no comments?

LordShryku

With this query, it shouldn't even come up if there are no comments. If you plan on using this with a join, and there will be null rows, you ifnull(field, 0)