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

Question

I have a database as follows:

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

I want to be able to create groups of the results but cannot figure out the quickest and most efficient way to do so without nesting MySQL queries. The result should look like (Styling ignored):

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

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

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

My code looks like:

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

This is a nested query that works, but is there a better way to do this?

Answer 1

One approach would be to query the database for all rows sorted by category, and then do the grouping into separate tables in PHP. You could either iterate over the rows keeping track of changes in category and output to HTML directly, or else build a structure of nested arrays that reflect the structure of the groups and the rows within each group and then use that to render your HTML.

Answer 2

1. Sort your recordset by the category column (then by module_name).
2. Loop through your recordset, keeping track of the category you're in ($current_category).
3. When the row's category doesn't match $current_category, you start a new grouping.

More specifically, adapting your code:

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

Answer 3

To me it looks like your laying out your datascheme in 3 tables and not 1 table with 3 columsn and multiple rows. In this scenariao:

Join your tables and group your results on category.

Answer 4

Just use an array. Unless your data set is ginormous.

foreach($results as $result)
    //assuming $result is an associative array
    $sorted_results[$result['category']][] = $result;
}

//now you can do fun stuff like
foreach($sorted_results as $category => $category_result_list) {
    //do something meaningful
}

Edit: Just saw your code sample