Select into a PHP array

Beauford2

I have a table which has categories, for example, ties, coats, pants.

What I want in the end is to have it display like the example below. I had this working using seperate queries for each category, but since in my real environment there are 8 categories, that is a lot of strain on the DB when your getting 100,000+ hits to the page a day. I want to do this using one query, and arrive at the same format as listed below.

I can't even begin to explain what I have tried, but nothing has worked so really isn't even worth mentioning. How do I go about this, as I am lost as how to do this.

Thanks for any input.

Ties

Black
Red
Blue

Pants

Short
Long
Dress

Coats

Winter
Summer
Spring

NogDog

You could retrieve them into a 2-D array, then loop through it for the output:

$sql = "SELECT DISTINCT category, choice FROM table ORDER BY category, choice";
$result = mysql_query($sql);
$data = array();
while($row = mysql_fetch_assoc($result))
{
   $data[$row['category'][] = $row['choice'];
}
foreach($data as $category => $choices)
{
   echo "<h3>".htmlentities($category)."</h3>\n<ul>\n";
   foreach($choices as $choice)
   {
      echo "<li>".htmlentities($choice)."</li>\n";
   }
   echo "</ul>\n";
}

Beauford2

Just to complicate things, I need everything in the table (i.e price, date, etc., etc,) plus I have a left join as I also need some info from another table.

The plot thickens.

NogDog

It's still the same process, just more columns to add to the array. You can just throw the entire result row array in:

while($row = mysql_fetch_assoc($result))
{
   $data[$row['category'][] = $row;
}

Then when you're outputting the data, you'd have an array of values:

foreach($data as $category => $items)
{
   echo "<h3>".htmlentities($category)."</h3>\n<table>\n";
   foreach($items as $item)
   {
      echo "<tr>";
      echo "<td>".htmlentities($item['name'])."</td>\n";
      echo "<td>".htmlentities($item['price'])."</td>\n";
      echo "<td>".htmlentities($item['and_so_forth'])."</td>\n";
      echo "</tr>\n";
   }
   echo "</table>\n";
}

Beauford2

So far so good, just put in some raw code but looks like it is exactly what I need. Thanks a million.