MySQL matches fields in one row

Question

I am working on a system to predict results, in which users are asked to predict who will win in 5 categories. I have all the data in the table, and I'm trying to figure out a way to count who had the most correct answers. I have an idea of ​​what is needed from the request, but my syntax is a mess. Can someone please help me?

<?php $res = array(1,2,3,4,5); //correct results // The idea is to count the number of table fields that match the $res array // and store that count in the 's' column $sql = mysql_query("SELECT *,s FROM awards_votes WHERE s+=IF( c1==".$res[0].",1,0), s+=IF( c2==".$res[1].",1,0), s+=IF( c3==".$res[2].",1,0), s+=IF( c4==".$res[3].",1,0), s+=IF( c5==".$res[4].",1,0) ORDER BY s DESC ") or die(mysql_error()); ?> 

Answer 1

You can simply do:

 SELECT awards_votes.*, (c1 = {$res[0]}) + (c2 = {$res[1]}) + (c3 = {$res[2]}) + (c4 = {$res[3]}) + (c5 = {$res[4]}) AS num_matches FROM awards_votes ORDER BY num_matches DESC 

This works because the boolean expression (c1 = somevalue) returns 0 or 1 in MySQL. Adding them together gives the total number of matches.