使用php分组查询

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!

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

Sort everything into arrays in your while loop. This should work:

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

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

   if ($produce_fruit_code == "Apple") {
   $apple_array[] = $variety_name;
   }
   if ($produce_fruit_code == "Banana") {
   $banana_array[] = $variety_name;
   }
   if ($produce_fruit_code == "Carrot") {
   $carrot_array[] = $variety_name;
   }

}

echo "Apples:" . implode(", ", $apple_array) . "<br/>";
echo "Bananas:" . implode(", ", $bananas_array) . "<br/>";
echo "Carrots:" . implode(", ", $carrots_array) . "<br/>";

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);

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

echo "<dl>";

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

    if($row['fruit_name'] != $current_fruit_name) {
        $current_fruit_name = $row['fruit_name'];
        echo "<dt>$current_fruit_name</dt>"; 
     }
echo "<dd>" . $row['variety_name'] . "</dd>";

}

    echo "";

If you want some CSS that will make a definition list look like the Name - X,Y,Z like in the question, let me know.

If you are using MySQL you can use the group_concat extension on grouping. Something along the lines of:

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;

or similar. Sorry my sql is a little rusty quite now. For more look at this article http://www.mysqlperformanceblog.com/2006/09/04/group_concat-useful-group-by-extension/ and of course here: http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html

If you dont work with MySQL and your database doesn't support group_concat think about buffering those results. In large databases and with many simultaneous users your application can considerably slow down when having to download all data and store it in locally every time.

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