How do i improve this code to make my code less trip to database?

Question

Hi,

I have written a code where i am frequently tripping to database which is kinda awkward, i am using the code to count the number of approves, pending, and spam in comments table.

here is my code.

 $query_approved = "SELECT COUNT(*) as approved FROM comments WHERE approve = '1'";
 $result_approved = mysql_query($query_approved);
 $row_approved = mysql_fetch_array($result_approved);
 $query_unapproved = "SELECT COUNT(*) as unapproved FROM comments WHERE approve = '0'";
 $result_unapproved = mysql_query($query_unapproved);
 $row_unapproved = mysql_fetch_array($result_unapproved);
 $query_spam = "SELECT COUNT(*) as spam FROM comments WHERE spam = '1'";
 $result_spam = mysql_query($query_spam);
 $row_spam = mysql_fetch_array($result_spam);

although that code works fine but it looks ugly. is there anyway to improve that?

Answer 1

use:

SELECT SUM(CASE WHEN c.approved = '1' THEN 1 ELSE 0 END) AS cnt_approved,
       SUM(CASE WHEN c.approved = '0' THEN 1 ELSE 0 END) AS cnt_unapproved,
       SUM(CASE WHEN c.spam = '1' THEN 1 ELSE 0 END) AS cnt_spam
  FROM COMMENTS c

Answer 2

You could combine:

SELECT SUM(approve) as approved, SUM(spam) AS spam, 
    SUM(approved) - COUNT(*) as unapproved 
FROM comments

Looking at the first three answers (including this), I lean toward Kelsey's approach as the most mantainable.

Answer 3

You can optimize first two call in one call

$query_approved = "SELECT COUNT(*) as totalCount , approve as status FROM comments group by approve";
 $result_approved = mysql_query($query_approved);
 $rows = mysql_fetch_array($result_approved);
foreach($rows as $row)
{
     if($row['status'] == '1')
     {
           $row_approved = $row['totalCount'];
     }
     elseif ($row['status'] == '0')
     {
           $row_unapproved = $row['totalCount'];
     }
}

 $query_spam = "SELECT COUNT(*) as spam FROM comments WHERE spam = '1'";
 $result_spam = mysql_query($query_spam);
 $row_spam = mysql_fetch_array($result_spam);

Answer 4

A more efficient single query might be:

$SQL = "SELECT approve, spam, count(*) as cnt FROM comments GROUP BY approve, spam";
$result_approved = mysql_query($SQL );
$rows= mysql_fetch_row($result_approved);

Then, you can foreach the $rows ...

foreach ( $rows as $row ) {
   $row[0] is the approved code (1 or 0)
   $row[1] is the spam flag (1 or 0)
   $row[2] is the count for this criteria
}

You'll end up with 4 rows, one each for approved that is spam, not spam, not approved that is spam and is not spam.