Grouping a query with php
- by Tom Hoad
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!
