php - MySQL/PHP : How to list results in groups but limit the DB queries required

Question

我有一个数据库如下:

---------------------------------------------------------------
|       module_name      |     category    |       content    |
---------------------------------------------------------------
|       module1          |     category1   |       content    |
|       module2          |     category1   |       content    |
|       module3          |     category2   |       content    |
|       module4          |     category3   |       content    |
|       module5          |     category2   |       content    |
---------------------------------------------------------------

我希望能够创建结果组，但无法在不嵌套 MySQL 查询的情况下找到最快、最有效的方法。结果应如下所示(忽略样式):

---------------------------------------------------------------
|       module1         |     category1    |       content    |
|       module2         |     category1    |       content    |
---------------------------------------------------------------

---------------------------------------------------------------
|       module3         |     category2    |       content    |
|       module5         |     category2    |       content    |
---------------------------------------------------------------

---------------------------------------------------------------
|       module4         |     category3    |       content    |
---------------------------------------------------------------

我的代码如下:

$query  = 'SELECT'
        . ' DISTINCT'
        . ' category AS name'
        . ' FROM #__table'
        . ' WHERE enabled = 1';
$db->setQuery($query);
$categories = $db->loadObjectList();
foreach ($categories as $category) {
$query  = 'SELECT'
    . ' module_name'
    . ' FROM #__table'
        . ' WHERE enabled = 1 AND category = \'' . $category->name . '\''
        . ' ORDER BY id';                       
    $db->setQuery($query);
    $results = $db->loadObjectList();
foreach ($results as $result) {
        echo $result->module_name;
    }
}

这是一个有效的嵌套查询，但有更好的方法吗？

Answer 1

1. 按类别对记录集进行排序列(然后按模块名称)。
2. 遍历你的记录集，保持跟踪您所在的类别($current_category).
3. 当行的类别不匹配 $current_category，你开始新分组。

更具体地说，调整您的代码:

$query  = 'SELECT'
        . ' module_name, category'
        . ' FROM #__table'
        . ' WHERE enabled = 1 '
        . ' ORDER BY category, id';                                               
$db->setQuery($query);
$results = $db->loadObjectList();

$current_category = null;
foreach ($results as $result) 
{
    if ($current_category != $result->category)
    {
        // whatever you do to separate the category listings
        //  if you don't do it before the first, check ($current_category != null)

        $current_category = $result->category
    }
    echo $result->module_name;
}