在一个查询中计算两个字段的问题

Question

guys i need to count new private messages and old one from a table

so first thing come to mind is using mysql_num_rows and easy thing to do

  // check new pms
  $user_id  = $userinfo['user_id'];
  $sql = "SELECT author_id  FROM bb3privmsgs_to WHERE user_id='$user_id' AND (pm_new='1' OR  pm_unread='1')";
  $result = $db->sql_query($sql) ;
  $new_pms = $db->sql_numrows($result);
  $db->sql_freeresult($result);

  // check old pms
  $sql = "SELECT author_id  FROM bb3privmsgs_to WHERE user_id='$user_id' AND (pm_new='0' OR  pm_unread='0')";
  $result = $db->sql_query($sql) ;
  $old_pms = $db->sql_numrows($result);
  $db->sql_freeresult($result);

but how can i count these two fields just in one statement and shorter lines ?~

Answer 1

Use this query instead:

SELECT SUM(CASE WHEN pm_new = '1' OR pm_unread = '1' THEN 1 ELSE 0 END) AS new_pms,
       SUM(CASE WHEN pm_new = '0' OR pm_unread = '0' THEN 1 ELSE 0 END) AS old_pms
  FROM bb3privmsgs_to
 WHERE user_id='$user_id'

Here's a MySQL-specific version that reads more cleanly:

SELECT COUNT(IF(pm_new = '1' OR pm_unread = '1', 1, NULL)) AS new_pms,
       COUNT(IF(pm_new = '0' OR pm_unread = '0', 1, NULL)) AS old_pms
  FROM bb3privmsgs_to
 WHERE user_id='$user_id'