Search code examples
phpmysqlsurvey

A neater way to count survey responses from mysql db

currently I using this code to count survey responses: 

$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";

as you can see - it's really messy! Any better way to do this? I've tried COUNT and GROUP BY, but can't get them to work. Thanks

Solution

  • You could try this SQL (which I haven't tested) -

    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

    To get the results into PHP you could try something like this (I can't test PHP code so I've based the code below on this code - http://php.net/manual/en/function.mysql-query.php and hoped for the best! The code below will almost certainly contain errors but will hopefully give you an idea of how it could be done)

    //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>"
}