hierarchical Mysql data into php

[wDev]

Ok, I seem to be running into this a lot lately and I don't know the most elegant way to handle it. Basically I have a data structure as follows:

 

CategoryItem

cat1item1

cat1item2

cat1item3

cat2item4

cat2item5

 

So the first column holds the category the the second column is in. What I want to do is print out a list of categories then underneath it have the items that it contains. For example the output would be:

 

cat1

• item1
  
• item2
  
• item3
  

cat2

• item4
  
• item5
  

 

What I am currently doing is basically this:

 

foreach($categories as $category) {
    echo '$category['name']';
    
    // select * from cateogory_items where cateogry = $category['name']
    
    foreach($category_items as $category_item) {
         echo $category_item['item'];
    }

}

 

Granted, that was a watered down example, but hopefully that can get my point across. So, my question is, that seems like a lot of database hits, is there an more elegant way to do the above?

 

[Barand]

<?php
$sql = "SELECT category, item FROM cateogory_items
        ORDER BY category, item";
$res = mysql_query($sql);
$prevcat='';
while (list($cat, $item) = mysql_fetch_row($res))
{
    if ($prevcat != $cat)
    {
        if ($prevcat != '') echo "</ul>\n";
        echo "<strong>$cat</strong><ul>\n";
    }
    echo "<li>$item</li>\n";
}
echo "</ul>\n";
?>