問題描述

為了盡可能少的保留SQL語句，我想從MySQL做select set:

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

處理 MySQL 結果的最佳和最有效的方法是什么?

What's the best and most efficent way to process MySQL result?

我認為類似(偽 PHP)

I thought something like (pseudo PHP)

foreach ($product = fetch__assoc($result)){
  $products[$category][] = $product;
}

然后在輸出時，做foreach循環:

and then when outputting it, do foreach loop:

foreach($categories as $category){
  foreach($products[$category] as $product){
    $output;
  }
}

這是最好的，還是像mysql_use_groupby之類的神奇東西?

Is this the best, or is something magical like mysql_use_groupby or something?

推薦答案

就像 mluebke 評論的那樣，使用 GROUP 意味著您只能獲得每個類別的一個結果.根據您提供的列表作為示例，我認為您想要這樣的東西:

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
}

然后遍歷數組.示例:

foreach ($list as $category => $products) {
  echo '<h1>' . $category . '</h1>';

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

}

這篇關于PHP/MySQL 按列分組結果的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！