PHP & MYSQL：使用 group by 分类
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2977213/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PHP & MYSQL: using group by for categories
提问by sam
My database has the following setup
我的数据库有以下设置
productid | productname | category id
and I want to output them like so:
我想像这样输出它们：
category #1
item 1
item 2
item 3
category #2
item 1
item 2
item 3
I used group by the group them together and that works fine, but I want to loop through each group and display the contents of that group. How would I do this?
我使用 group by 将它们组合在一起，效果很好，但我想遍历每个组并显示该组的内容。我该怎么做？
回答by Bill Karwin
I'd recommend just a simple query to fetch all the rows, sorted by category id. Output the category only if its value changes from the previous row.
我建议只使用一个简单的查询来获取按类别 ID 排序的所有行。仅当类别的值与上一行发生变化时才输出类别。
<?php
$stmt = $pdo-> query("SELECT * FROM `myTable` ORDER BY categoryID");
$current_cat = null;
while ($row = $stmt->fetch()) {
if ($row["categoryID"] != $current_cat) {
$current_cat = $row["categoryID"];
echo "Category #{$current_cat}\n";
}
echo $row["productName"] . "\n";
}
?>
回答by 2ndkauboy
This should work:
这应该有效：
$categories = array();
$result= mysql_query("SELECT category_id, product_name FROM `table` GROUP BY `catagory_id` ORDER BY `catagory_id`");
while($row = mysql_fetch_assoc($result)){
$categories[$row['category_id']][] = $row['product_name'];
}
// any type of outout you like
foreach($categories as $key => $category){
echo $key.'<br/>';
foreach($category as $item){
echo $item.'<br/>';
}
}
The output you can style yourself. You simply add everything into a multidimensional array with the category id as the first level keys.
您可以自己设置样式的输出。您只需将所有内容添加到以类别 id 作为第一级键的多维数组中。
EDIT: The resulting array might look like this:
编辑：生成的数组可能如下所示：
$categories = array(
'cateogy_id_1' => array(
1 => 'item_1',
2 => 'item_2',
...
),
'cateogy_id_2' => array(
1 => 'item_1',
2 => 'item_2',
...
),
....
);
回答by Marcus Adams
What you want is to order them by the category, not group them.
您想要的是按类别对它们进行排序，而不是将它们分组。
SELECT * FROM MyTable
ORDER BY category_id, product_id
When you iterate through the list, just output a new header whenever the category_id changes.
当您遍历列表时，只要 category_id 更改就输出一个新标题。
回答by 2MAS
This approach does not need the data to be sorted by category_id.
这种方法不需要按 category_id 对数据进行排序。
You can use php to group each category together in a nested array. After this, the data can be easily be displayed in a table, passed to a grap/chart library, etc...
您可以使用 php 将每个类别组合在一个嵌套数组中。在此之后，数据可以很容易地显示在表格中，传递到图表/图表库等......
<?php
$stmt = $pdo-> query("SELECT * FROM myTable ORDER BY categoryID");
$categories = []; //the array to hold the restructured data
//here we group the rows from different categories together
while ($row = $stmt->fetch())
{
//i'm sure most of you will see that these two lines can be performed in one step
$cat = $row["categoryID"]; //category id of current row
$categories[$cat][] = $row; //push row into correct array
}
//and here we output the result
foreach($categories as $current_cat => $catgory_rows)
{
echo "Category #{$current_cat}\n";
foreach($catgory_rows as $row)
{
echo $row["productName"] . "\n";
}
}
?>
回答by suraj kumar
$stmt = $dbConnection->prepare("SELECT exam_id, COUNT(report_id) FROM bug_report GROUP BY exam_id; ");
//$stmt->bind_param('s',$exam_id);
$stmt->execute();
$extract = $stmt->get_result();
$count = $extract->num_rows;
if($count){
while($rows = $extract->fetch_assoc()){
$exam_id = $rows["exam_id"];
switch($exam_id){
case "jee_questions":
$jeeBugCount = $rows["COUNT(report_id)"];
break;
case "gate_questions":
$gateBugCount = $rows["COUNT(report_id)"];
break;
}
}
}
回答by cbednarski
The easiest way is probably to pull the list of categories, iterate through and pull their attached products. (I.e. several queries.)
最简单的方法可能是拉出类别列表，迭代并拉出其附加产品。（即几个查询。）
For instance (pseudocode):
例如（伪代码）：
$result = fetch_assoc("SELECT category_id FROM categories");
foreach($result as $row)
{
echo $row['category_id'];
$result2 = fetch_assoc("SELECT product_id FROM products");
foreach($result2 as $row2)
{
echo $row2['product_id'];
}
}
If you want to do it all in one query you can do something like:
如果您想在一个查询中完成所有操作，您可以执行以下操作：
$result = fetch_assoc("SELECT product_id, category_id FROM products p JOIN categories c ON p.category_id = c.category_id ORDER BY c.category_id ASC");
$last = null;
foreach($result as $row)
{
# Output the category whenever it changes
if($row['category_id'] != last)
{
echo $row['category_id'];
$last = $row['category_id'];
}
echo $row['item_id'];
}
Then you can iterate over the result set and pull out the category name whenever it changes.
然后，您可以迭代结果集，并在类别名称发生变化时取出类别名称。
There may be a more sophisticated, elegant way to write the SQL to do everything before you get it from the database, but I'm not that smart. ;)
可能有一种更复杂、更优雅的方式来编写 SQL 以在从数据库中获取它之前完成所有操作，但我没有那么聪明。;)
Note: Examples use pseudo code. I use a mysql abstraction class that works like:
注意：示例使用伪代码。我使用一个 mysql 抽象类，其工作方式如下：
$db->query("SELECT stuff");
$db->multi_result(); // returns 2d-associative array
I can then foreach($db->multi_result() as $row), which is super lazy and awesome.
然后我可以foreach($db->multi_result() as $row)，这是超级懒惰和很棒的。
I can post the code for the abstraction layer if you like.
如果您愿意，我可以发布抽象层的代码。
