一种更简洁的方法来计算 mysql 数据库的调查响应

Question

目前我使用此代码来计算调查回复：

$VERYHAPPY = mysql_query("SELECT * FROM answers, complete WHERE (answers.uniqueID = complete.uniqueID) AND (complete.timestamp) IS NOT NULL AND (answers.surveyID='$surveyID') AND QID = '$QID' AND response = 'VERY HAPPY' ");
$HAPPY = mysql_query("SELECT * FROM answers, complete WHERE (answers.uniqueID = complete.uniqueID) AND (complete.timestamp) IS NOT NULL AND (answers.surveyID='$surveyID') AND QID = '$QID' AND response = 'HAPPY' ");
$DONTKNOW = mysql_query("SELECT * FROM answers, complete WHERE (answers.uniqueID = complete.uniqueID) AND (complete.timestamp) IS NOT NULL AND (answers.surveyID='$surveyID') AND QID = '$QID' AND response = 'DONT KNOW' ");
$UNHAPPY = mysql_query("SELECT * FROM answers, complete WHERE (answers.uniqueID = complete.uniqueID) AND (complete.timestamp) IS NOT NULL AND (answers.surveyID='$surveyID') AND QID = '$QID' AND response = 'UNHAPPY' ");
$VERYUNHAPPY = mysql_query("SELECT * FROM answers, complete WHERE (answers.uniqueID = complete.uniqueID) AND (complete.timestamp) IS NOT NULL AND (answers.surveyID='$surveyID') AND QID = '$QID' AND response = 'VERY UNHAPPY' ");

 $VERYHAPPYcount = mysql_num_rows($VERYHAPPY);
 $HAPPYcount = mysql_num_rows($HAPPY);
 $DONTKNOWcount = mysql_num_rows($DONTKNOW); 
 $UNHAPPYcount = mysql_num_rows($UNHAPPY);
 $VERYUNHAPPYcount = mysql_num_rows($VERYUNHAPPY); 
  echo "VERY HAPPY = $VERYHAPPYcount</br>HAPPY = $HAPPYcount</br>DON'T KNOW = $DONTKNOWcount</br>UNHAPPY = $UNHAPPYcount</br>VERY UNHAPPY = $VERYUNHAPPYcount";  

如您所见 - 这真的很混乱！有更好的方法来做到这一点吗？我尝试过 COUNT 和 GROUP BY，但无法让它们工作。谢谢

Answer 1

你可以尝试这个SQL（我没有测试过） -

SELECT count(*) as recordcount, response 
FROM answers, complete 
WHERE (answers.uniqueID = complete.uniqueID) 
AND (complete.timestamp) IS NOT NULL 
AND (answers.surveyID='$surveyID') 
AND QID = '$QID'
GROUP BY response

要将结果输入PHP，你可以尝试这样的事情（我无法测试PHP代码，所以我基于此代码编写了下面的代码 - http://php.net/manual/en/function.mysql-query.php并祝愿最好的结果！下面的代码几乎肯定会包含错误，但希望能给您带来帮助一个关于如何做到这一点的想法）

//assign sql above to $query variable prior to doing this
$result = mysql_query($query);

while ($row = mysql_fetch_assoc($result)) {
    echo $row['response'] & " = " & $ row['recordcount'] & "count</br>"
}