用php分组查询 [英] Grouping a query with php

问题描述

基本问题！

我有2张桌子

生产

   +-----+--------------+  
   | id  |  fruit_name  |
   +--------------------+
   | 1   |   Apple      |
   | 2   |   Banana     |
   | 3   |   Carrot     |
   +-----+--------------+

品种

   +-----+---------------+----------------+
   | 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

我当前的代码是

$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/>';

}

输出:

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

这篇关于用php分组查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！