使用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

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']; 
  }
}