current community

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 1 down vote favorite
2

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.

share|improve this question
2  
By the way, don't you know mysql_** functions are deprecated? You'd better use PDO instead. –  CertaiN Sep 26 '13 at 13:26
1  
There are easy ways to do it in PHP by flattening the array, but the proper way to handle this would be to use a COUNT() aggregate: SELECT poll1, COUNT(*) AS count FROM names GROUP BY poll1 –  Michael Berkowski Sep 26 '13 at 13:27
 
What do you mean by And echo results (minimum value 0 (default) and maximum 3):? Are you actually trying to find the value that's repeated most, or something else? –  Amal Murali Sep 26 '13 at 13:27
 
Please try with this query SELECT poll1, COUNT(*) AS cnt FROM names WHERE poll1 BETWEEN 1 AND 3 GROUP BY poll1; –  Jigar Chaudhary Sep 26 '13 at 13:31
add comment

1 Answer

up vote 2 down vote accepted

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)
share|improve this answer
 
Works perfect. Any changes if i'll have 18 of polls in one page? Like poll1, poll2, poll3, ... , poll18 –  oboshto Sep 26 '13 at 13:58
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.