find most common answer

Question

I've been following an online tutorial to build a quiz which calculates the result from an sql table.

The way the quiz currently works is to create a string depending upon how often each category in the table appears but what I'd like to try and do is populate the string with the single most commonly occuring category.

Can anyone help me achieve this?

My code so far:

$query = "SELECT result FROM quiz_key WHERE question IN ('$Q1','$Q2','$Q3','$Q4','$Q5','$Q6','$Q7','$Q8','$Q9','$Q10')";
$result = mysql_query($query);

$cat_a = $cat_b = $cat_c = $cat_d = $cat_e = $cat_f = 0;
while($row = mysql_fetch_array($result)) {
    $cat = $row['category'];
    if ($cat == "A") {
        $cat_a += 1;
    } elseif ($cat == "B") {
        $cat_b += 1;
    } elseif ($cat == "C") {
        $cat_c += 1;
    } elseif ($cat == "D") {
        $cat_d += 1;
    } elseif ($cat == "E") {
        $cat_e += 1;
    } elseif ($cat == "F") {
        $cat_f += 1;
    }
}

$array = array('A' => $cat_a, 'B' => $cat_b, 'C' => $cat_c, 'D' => $cat_d, 'E' => $cat_e, 'F' => $cat_f);
$str = "";

Then followed by this:

foreach ($array as $i => $value) {
    if ($value >= 6) {      
        $str = $i;
        break;
    } elseif ($value >= 3) {        
        $str .= $i;
    }
}

$var = sort($str);

Any help would be much appreciated.

Whilst I've found a number of other posts on finding the most common value in a database, I don't understand what to do with the information, hence askng for some help. — bigdave, Jan 18 '16 at 13:20
you could try using `SELECT result, count(result) FROM quiz_key WHERE question IN ('$Q1','$Q2','$Q3','$Q4','$Q5','$Q6','$Q7','$Q8','$Q9','$Q10')`, you then could search for the biggest number, and so have the most common — Lino, Jan 18 '16 at 13:23

score 2 · Accepted Answer · answered Jan 18 '16 at 13:39

As @Lino already pointed out, if you don't care about the question and want so count the best answer overall you need COUNT(result) and in addition you need a GROUP BY result statement

I just assumed how your table could look like

And with the COUNT-GROUP BY Statement you get the following result

(note: if you want the answer with the highest count on top, add ORDER BY to the statement)

When executing this SQL-Query you can get the result as an array in PHP to work with it.

find most common answer

1 Answers1