Help exploding and count values.

jej07
jej07 used Ask the Experts™
on
I have MySQL database fields that can contain up to 3 values, separated by a comma (Red,Blue,Yellow). I would like to count how many times each value appears. So, the results would be Red 4, Blue 3, Yellow 2.

id      | color
1      | Red,Blue
2      | Red,Blue
3      | Red,Yellow
4      | Red,Blue,Yellow

It seems like I would have to loop through each row, exploding the string; then loop through the results to count the values. But I'm not even sure where to begin doing that.

$result = mysql_query("SELECT color FROM cars");
$row = mysql_fecth_array($result);

$favColors= explode(',',$row['color']);

$count = count($favColors);

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Expert of the Year 2008
Top Expert 2008
Commented:
try:
$result = mysql_query("SELECT color FROM cars");
$total=array();
while( $row = mysql_fecth_assoc($result) )
{
      $favColors= explode(',',$row['color']);

     foreach($favColors as $v)
     {
       $total[$v]=isset($total[$v]) ? $total[$v]+1: 1;
     }
}

print_r($total);

Open in new window

Author

Commented:
That's not exactly the route I thought I needed to take, but it worked perfectly. Thank you very much for your help.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial