Grouping a query with php

Posted by Tom Hoad on Stack Overflow See other posts from Stack Overflow or by Tom Hoad
Published on 2012-04-06T23:04:45Z Indexed on 2012/04/06 23:28 UTC
Read the original article Hit count: 362

Filed under:
|

Basic question!

I have 2 tables

FRUIT

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!

© Stack Overflow or respective owner

Related posts about php

Related posts about mysql