PHP和MYSQL：使用group by作为类别

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?

我把它们组合在一起使用,并且工作正常,但我想循环遍历每个组并显示该组的内容。我该怎么办?

6 个解决方案

#1

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";
}

?>

#2

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',
        ...
    ),
    ....
);

#3

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更改,只需输出一个新标头。

#4

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在嵌套数组中将每个类别组合在一起。在此之后,数据可以轻松地显示在表格中,传递到grap /图表库等...

<?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";
    }
}
?>

#5

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之前,可能有一种更复杂,更优雅的方式来编写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()为$ row),这是超级懒惰和真棒。

I can post the code for the abstraction layer if you like.

如果你愿意,我可以发布抽象层的代码。

#6

$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;
                }
            }   
        }

#1