PHP / MySQL组按列结果 [英] PHP/MySQL group results by column

问题描述

为了保持尽可能少的SQL语句，我想从MySQL中进行选择：

  SELECT * FROM产品类别IN（10,120,150,500）ORDER BY category，id; 
  

现在，我有以下方式的产品清单：

 类别
  - 产品1 
  - 产品2 
类别2 
  - 产品37 
 ... 
  

处理MySQL结果的最好和最有效的方法是什么？

我认为类似于（伪PHP）

  foreach（$ product = fetch__assoc（$ result））{
 $产品[$ category] ​​[] = $ product; 
} 
  

然后输出时，做foreach循环：

  foreach（$ categories as $ category）{
 foreach（$ products [$ category] ​​as $ product）{
 $ output ; 
 
 
 
 
 
 
 $ b 
这是最好的，还是像 mysql_use_groupby 或其他东西？
解决方案像 mluebke 评论，使用GROUP意味着您只为每个类别获得一个结果。根据你列举的例子，我想你想要的是这样的：
  $ sql =SELECT * FROM products类别IN（10,120,150,500）GROUP BY category ORDER BY category，id; 
 $ res = mysql_query（$ sql）; 
 
 $ list = array（）; 
 while（$ r = mysql_fetch_object（$ res））{
 $ list [$ r-> category] ​​[$ r-> id] ['name'] = $ r-> name; 
 $ list [$ r-> category] ​​[$ r-> id] ['whatever'] = $ r-> whatever; 
 // etc 
}  
然后遍历数组。示例： 
 
 
  foreach（$ list为$ category => $ products）{
 echo'< h1>' 。 $类别。 < / H1>; 
 
 foreach（$ product为$ productId => $ productInfo）{
 echo'Product'。 $ productId。 '：'。 $ productInfo [名]; 
 // etc 
} 
 
}  
 
in order to keep as few SQL statements as possible, I want to do select set from MySQL:
SELECT * FROM products WHERE category IN (10,120,150,500) ORDER BY category,id;
Now, I have list of products in following manner:
CATEGORY
 - product 1
 - product 2
CATEGORY 2
 - product 37
...
What's the best and most efficent way to process MySQL result?


I thought something like (pseudo PHP)
foreach ($product = fetch__assoc($result)){
  $products[$category][] = $product;
}
and then when outputting it, do foreach loop:
foreach($categories as $category){
  foreach($products[$category] as $product){
    $output;
  }
}
Is this the best, or is something magical like mysql_use_groupby or something?
解决方案
Like mluebke commented, using GROUP means that you only get one result for each category. Based on the list you gave as an example, I think you want something like this:
$sql = "SELECT * FROM products WHERE category IN (10,120,150,500) GROUP BY category ORDER BY category, id";
$res = mysql_query($sql);

$list = array();
while ($r = mysql_fetch_object($res)) {
  $list[$r->category][$r->id]['name'] = $r->name;
  $list[$r->category][$r->id]['whatever'] = $r->whatever;
  // etc
}
And then loop through the array. Example:
foreach ($list as $category => $products) {
  echo '<h1>' . $category . '</h1>';

  foreach ($products as $productId => $productInfo) {
    echo 'Product ' . $productId . ': ' . $productInfo['name'];
    // etc
  }

}


                        
这篇关于PHP / MySQL组按列结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！