current community

your communities

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

I have a mySQL database table with a column that corresponds to an image that has been voted on. The values are 001.jpg - 013.jpg. I need to figure out how many times each image has been voted for. I tried the following code, and got a count for 002.jpg, but none of the other images. There are at least 25 votes as of this moment. Here is the code I tried to use:

<?php
$db = mysql_connect("xxx", "xxx", "xxx");
mysql_select_db("club",$db);
$q = mysql_query("SELECT image FROM january");
$array = mysql_fetch_array($q);
$results = array_count_values($array);
         for each
         while (list ($key,$val) = each($results)) {
         echo "$key -> $val <br>";
         }
?>

I understand that array_count_values() is the way to count the occurrence of each vote, but all the examples I can find don't show how to pair this with mySQL. Any help would be appreciated!

share|improve this question

1 Answer 1

up vote 3 down vote accepted

You need GROUP BY and COUNT():

SELECT image, COUNT(*) as votes FROM january GROUP BY image

This will return two columns: 1 for the image, and 1 for the number of votes for this image:

image     votes
001.jpg   1
002.jpg   32
...

Full code:

$db = mysql_connect("xxx", "xxx", "xxx");
mysql_select_db("club",$db);
$q = mysql_query("SELECT image, COUNT(*) as votes FROM january GROUP BY image");

$votes = array();
while ($row = mysql_fetch_assoc($q)) {
    $votes[$row['image']] = $row['votes'];
}

// $votes is an array of 'image' => 'votes'

foreach($votes as $image => $count) {
    echo "$image: $count<br>";
}
share|improve this answer
    
This is great...thanks! I can't quite figure out an echo statement to get this written to the web page. Can you help with that? –  GoNorthWest Feb 1 '11 at 21:19
    
foreach($votes as $image => $count) { echo "$image: $count<br>"; } –  arnaud576875 Feb 1 '11 at 21:21
    
Very awesome...thanks! I appreciate everyone who took time to help out with this. I learn something new each time! –  GoNorthWest Feb 1 '11 at 21:25

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.