Grouping a query with php

Question

Basic question!

I have 2 tables

PRODUCE

   +-----+--------------+  
   | id  |  fruit_name  |
   +--------------------+
   | 1   |   Apple      |
   | 2   |   Banana     |
   | 3   |   Carrot     |
   +-----+--------------+

VARIETIES

   +-----+---------------+----------------+
   | id  |  fk_fruit_id  |  variety_name  |
   +-----+---------------+----------------+
   | 1   |   1           |    Cox         |
   | 2   |   1           |    Braeburn    |
   | 3   |   2           |    Chester     |
   | 4   |   3           |    Kotaka      |
   | 5   |   3           |    Imperial    |
   | 6   |   3           |    Oneal       |
   +-----+---------------+----------------+

I'd like to output a list of varieties per fruit e.g.

APPLE - Cox, Braeburn

BANANA - Chester

CARROT - Kotaka, Imperial, Oneal

My current code is

$query   = "SELECT * FROM produce, varieties WHERE produce.id = varieties.fk_fruit_id"; 

$result  = mysql_query($query) or die('Error : ' . mysql_error()); 
while ($row     = mysql_fetch_array($result, MYSQL_ASSOC)) { 

$produce_fruit_code   = $row['fruit_code']; 
$variety_name   = $row['variety_name']; 

echo $produce_fruit_code.' - '.$variety_name.'<br/>';

}

which outputs:

Apple - Cox
Apple - Braeburn
Banana - Chester
Carrot - Kotaka
Carrot - Imperial
Carrot - Oneal

Not a million miles away, but still not there. Any help is much appreciated, thanks!

Answer 1

You probably could get one chunky sql statement to do that for you but I'd opt for data juggling with arrays.

For example (not tested and excuse the formatting):

$query   = "SELECT * FROM produce, varieties WHERE produce.id = varieties.fk_fruit_id";     
$result  = mysql_query($query) or die('Error : ' . mysql_error()); 

$res=array();

while ($row     = mysql_fetch_array($result, MYSQL_ASSOC)) { 

    $produce_fruit_code   = $row['fruit_code']; 
    $variety_name   = $row['variety_name']; 

    if(isset($res[$produce_fruit_code])){
        $res[$produce_fruit_code]+=','.$variety_name;
    }else{
        $res[$produce_fruit_code]=$variety_name;
    }

}        
print_r($res);