Foreach Loop using MySQL Query?

Question

I ran into this problem earlier but I didn't ask it because I found a work around, however Im not so lucky this time.

I am trying to create a simple list for each "pg.pcat" (page category) to show 5 latest links from each category (I am aware I didn't add LIMIT or ORDER BY).

$q2 = "
SELECT pg.pcat, p.page_id, p.link_title 
FROM pages AS P 
INNER JOIN page_categories AS pg ON pg.pc_id = $sidenav
";

$r2 = @mysqli_query ($dbc, $q2); // Run the Query.  

while ($qarr = mysqli_fetch_array($r2, MYSQLI_ASSOC)) {
    if(!isset($printed)) { 
        echo "<div class=\"sideNavSpan\">{$qarr['pcat']}</div>";
        $printed = true;
    }
    echo "
    <li>
     <a href=\"page.php?pid={$qarr['page_id']}\">{$qarr['link_title']}</a>
    </li>
    ";
}

I need something like this but I can't figure out how to use a for loop on the values in $qarr.

----pg.pcat1
p.link_title
p.link_title
p.link_title
p.link_title
p.link_title

----pg.pcat2
p.link_title
p.link_title
p.link_title
p.link_title
p.link_title

----pg.pcat3
p.link_title
p.link_title
p.link_title
p.link_title
p.link_title

etc. help? please and thank you :)

Answer 1

You'll need to order by pg.pcat in your SQL query, and wrap the whole of this in an if test to ensure that at least one row has been returned

$currCat = '';
$printed = false;
while ($qarr = mysqli_fetch_array($r2, MYSQLI_ASSOC)) {     
   if($currCat != $qarr['pcat']) {  // category has changed
      if ($printed) { // we already printed an opening <ul> so we need to close it
         echo '</ul>';
         $printed = true;
      }
      echo"<div class=\"sideNavSpan\">{$qarr['pcat']}</div><ul>";
      $currCat = $qarr['pcat'];
   }
   echo "<li><a href=\"page.php?pid={$qarr['page_id']}\">{$qarr['link_title']}</a></li>";
}
echo '</ul>';