Conditional counting of records

Question

Am trying to calculate the number of rows in a table depending on a certain condition. So, I did manage to write a piece of code that would function as required.

But, it's bit too long. So am wondering if there is any easier way to achieve it.

Code:

// Comments
$sql_total_comments = mysql_query("SELECT * FROM comments");
$sql_pending_comments = mysql_query("SELECT * FROM comments WHERE comment_status = '0'");
$sql_approved_comments = mysql_query("SELECT * FROM comments WHERE comment_status = '1'");
$sql_declined_comments = mysql_query("SELECT * FROM comments WHERE comment_status = '2'");

$total_comments_num = mysql_num_rows($sql_total_comments);
$pending_comments_num = mysql_num_rows($sql_pending_comments);
$approved_comments_num = mysql_num_rows($sql_approved_comments);
$declined_comments_num = mysql_num_rows($sql_declined_comments);

Answer 1

Use COUNT() DB FUNCTION to do such job.

$ret = mysql_query("SELECT COUNT(*) FROM comments");
$row = mysql_fetch_row($ret);
$total_comments_num = $row[0];