Looping through a JOIN'ed MySQL Associative Array

Question

Right, so I've got a query that looks like this:

$bestof_query = "SELECT * FROM physicians p JOIN (awards a, categories c, awardLevels l) ON (a.id = p.id AND c.id = a.category AND l.id = a.level) ORDER BY a.category, a.level ASC ";
$query = mysql_query($bestof_query);

while($result = mysql_fetch_array($query)) {
 extract($result);
 echo "<h3>" . $category . "<h3>";
   foreach($category as $value) {
       echo "<h5>" . $name . "<h5>" . "<p>" . $description . "</p>" . "\r\n";
   }
}

A typical result looks like-

Array (
 [0] => 39  [id] => 1
 [1] => William�B.�Poff [name] => William�B.�Poff
 [2] => 10 South Main Street [address1] => 10 South Main Street
 [3] => Tower A, Suite 1400 [address2] => Tower A, Suite 1400 
 [4] => Jackson [city] => Jackson 
 [5] => VA [state] => VA
 [6] => 24111 [zip] => 24111
 [7] => downtown-jackson [neighborhood] => downtown-jackson
 [8] => 5409837649 [phone] => 5401111111
 [9] => http://www.foo.com [uri] => http://www.foo.com 
 [10] => Foo�Rogers,�PLC [firm] => Foo�Rogers,�PLC
 [11] => 39 
 [12] => 1 [category] => Bankruptcy
 [13] => 1 [level] => 1 
 [14] => 2009 [year] => 2009 
 [15] => 1 
 [16] => Bankruptcy 
 [17] => 1 
 [18] => Platinum [description] => Platinum )

I'm trying to loop through based on the [category] and while the category is equal to Bankruptcy, then output all the results for bankruptcy, then move on to the next category.

Does that make sense? I've never been very good with loops so thanks in advance!

What I am hoping for is-

<h3>$category</h3>
<ul>
<li>$name - $description</li>
<li>$name - $description</li>
<li>$name - $description</li>
<li>$name - $description</li>
</ul>

And then iterate to the next category. I think a nested loop, but I'm not sure how to do the nested loop.

Answer 1

$query = "SELECT * FROM physicians p JOIN (awards a, categories c, awardLevels l) ON (a.id = p.id AND c.id = a.category AND l.id = a.level) ORDER BY a.category, a.level ASC ";
$query = mysql_query($query);

while($row = mysql_fetch_assoc($results)) { 
      $categories[$row['category']][] = $row;
}

foreach($categories as $key=>$value){
    echo"<h3>$key</h3>\r\n";
    echo "<div><ul>\r\n";
    foreach($value as $physician){
    echo "  <li>".$physician['name']." - ".$physician['description']."</li>\r\n";
    }
    echo "</ul></div>\r\n"
}

Answer 2

You need a simple state machine. This is a way of doing a nested loop without doing a real nested loop. Especially as the data-stream is a single-dimension.

$category = '';
while( $result = mysql_fetch_array($query) ) {
    if( $result['category'] != $category ) {
        $category = $result['category'];
        echo "<h3>".$category."</h3>\n";
    }

    echo "<h5>".$result['name']."</h5>\n";
    echo "<p>".$result['description']."</p>\n";
}

It gets more complex if you have markup for each group as a whole, but this is the basic idea. It relies on the fact that that everything in the same category is together, which you achieve with the ORDER BY.

P.S: don't use extract(); it pollutes your namespace.