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 :)

FYI - You're missing the closing </li> tag. Also aren't you going to wrap your list items in a list like `<ul>'? — Naveed Ahmad
– Naveed Ahmad, Commented Apr 21, 2011 at 14:45
@Naveed - In HTML AFAIK you do not need the closing tag for LI's. In XHTML you do, but not in HTML. Although it is a good practice to do so (I added it in my edit before you mentioned it, btw). — Jared Farrish
– Jared Farrish, Commented Apr 21, 2011 at 14:48

Mark Baker · Accepted Answer · 2011-04-21 14:52:30Z

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>';

answered Apr 21, 2011 at 14:52

Mark Baker

213k34 gold badges354 silver badges390 bronze badges

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

9 Comments

Jared Farrish Over a year ago

@Mark Baker - What about a GROUP BY? Would that also help?

laphiloctete Over a year ago

I see where you're going with this, i tried to do something similar incrementing $sidenav which was my equivalent to your currcat. I thought a foreach loop would be the way to go, but i think ill stick with this method.

Jared Farrish Over a year ago

@luckycypher - Make sure and click the outlined checkmark under this answer's vote tally if it's the answer you use. :)

Mark Baker Over a year ago

@Jared - Nope, GROUP BY is used when you want to aggregate values from several rows into a single returned row

Jared Farrish Over a year ago

@Mark Baker - Thanks. Can you tell I've not really used it a whole lot?

|

Collectives™ on Stack Overflow

Foreach Loop using MySQL Query?

1 Answer 1

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related