April 7, 2012
Grouping a query with php
Question by Tom Hoad
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 by Nathaniel Ford
This won’t get you all the way, but it will get you most of what you want. There are some edge cases that are problematic.
$query = "SELECT * FROM produce, varieties WHERE produce.id = varieties.fk_fruit_id";
$result = mysql_query($query) or die('Error : ' . mysql_error());
$produce_fruit_code = "";
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
if ($produce_fruit_code != $row['fruit_code'])
{
$produce_fruit_code = $row['fruit_code'];
echo "<br/>".$produce_fruit_code." - ". $row['variety_name'];
} else {
echo ", ".$row['variety_name'];
}
}
Answer by Starx
You can query this directly
SELECT
f.fruitname as fruit,
GROUP_CONCAT(distinct v.varietyname separator ',') as variety
FROM fruit f JOIN varieties v ON produce.id = varieties.fk_fruit_id;
GROUP BY produce.id
