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.

staticsan · Accepted Answer · 2010-03-05 04:08:13Z

3

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.

answered Mar 5, 2010 at 4:08

staticsan

30.6k5 gold badges64 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Marty Over a year ago

Thanks, I really appreciate the assistance. I'll keep the advice in mind on extract(). It's convenient though but I get what you're saying, especially when dealing with multiple result sets I guess right?

Marty Over a year ago

What if I wanted to wrap the results (not the conditional if output) in a <div>? Like directly before the <h3> I could put the opening <div> but how would I handle the </div>?

staticsan Over a year ago

You extend the state-machine a bit. You can easily close a <div> just before you open the next one, but then you have to remember two things: to close the last one at the end of the loop and to open a 'dummy' one at the start of the loop. You can do tricks with variables that cut down on that, if you like.

staticsan Over a year ago

The other major alteration to this type of loop is to shift the termination check into the middle of the loop. It is not obvious how to do this in this example, I'm afraid. But once you start duplicating code, remember this possibility because it will then provide a very elegant solution to part of the problem.

mcgrailm · Accepted Answer · 2010-03-05 14:38:13Z

2

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

edited Mar 5, 2010 at 14:38

answered Mar 5, 2010 at 2:11

mcgrailm

17.7k22 gold badges85 silver badges131 bronze badges

11 Comments

Marty Over a year ago

I've never used GROUP BY .. but in a quick test I don't think I'm using it right because instead of getting 200+ results as before now I'm only getting 37. I tried this: 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) GROUP BY a.category

Marty Over a year ago

Isn't that what I was already doing with: "ORDER BY a.category, a.level ASC" ? My question is how to accomplish the loop, I think I've got the MySQL right or no?

Marty Over a year ago

well that's outputting everything yes, but I think I need a nested loop so I can output the category, then loop the names in that category, then move to the next category. I'll edit the original post to show what I mean.

Marty Over a year ago

hmm...that's not working, it's outputting: <ul> <li>3 - 3</li> <li>R - R</li> <li>1 - 1</li> <li>S - S</li> <li>R - R</li> <li>V - V</li> <li>2 - 2</li> <li>o - o</li> <li>2 - 2</li> <li>U - U</li> <li> - </li> <li>U - U</li> <li>3 - 3</li> <li>2 - 2</li> <li>S - S</li> </ul>

mcgrailm Over a year ago

can you show me print_r($categories); before the foreach loop

|

Collectives™ on Stack Overflow

Looping through a JOIN'ed MySQL Associative Array

2 Answers 2

4 Comments

11 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

11 Comments

Your Answer

Sign up or log in

Post as a guest

Related