MySQL+PHP array_count_values

Question

I need to find the most common value (not 0) from arrays. My code:

   include ("db.php");
   $query = "SELECT poll1 FROM names";
   $res = mysql_query($query) or die(mysql_error());

    while ($row = mysql_fetch_array($res)) {
       echo  $row['poll1'];
    }

And echo results (minimum value 0 (default) and maximum 3):

1
1
0
0
0
2
3

The most common value is "1". I cant use array_count_values, because there are 7 arrays of numbers.

Answer 1

This is a problem you should solve with SQL.

Firstly, you want to get the number of people selecting each option:

SELECT poll1, COUNT(*) AS count FROM names GROUP BY poll1;
+-------+-------+
| poll1 | count |
+-------+-------+
|     0 |     3 |
|     1 |     2 |
|     2 |     1 |
|     3 |     1 |
+-------+-------+
4 rows in set (0.00 sec)

Ok, but you're not interested in zeros, and you only care about the row with the largest value of count so you should sort by descending count, and limit it to 1 result:

SELECT poll1, count(*) AS count FROM names
WHERE poll1 != 0
GROUP BY poll1
ORDER BY count DESC
LIMIT 1;
+-------+-------+
| poll1 | count |
+-------+-------+
|     1 |     2 |
+-------+-------+
1 row in set (0.00 sec)