while loop within foreach/while loop....

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
joel24
Forum Newbie
Posts: 10
Joined: Tue Feb 03, 2009 6:18 am

while loop within foreach/while loop....

Post by joel24 »

Hi,
I've got some rows inside a mysql database table, I want to group them via a column called 'resource' and have that 'resource' displayed at the top of each group.
The 'resource' element is created from a user-input text field - I have no way of knowing what it will be, but some entries will belong to the same resource.

At the moment I have tried using two approaches;

The first called the mysql_fetch_array on the table twice, with one "SELECT DISTINCT resource FROM table" and then put those elements into an array ($res):

Code: Select all

 
<?php
[color=#00BF00]foreach[/color] ($res as $l) {
    [color=#0000FF]echo[/color] [color=#FF0000]"<tr><td>$l</td></tr>"[/color];
        [color=#FFBF00]//list row data within resource type[/color]
        [color=#00BF00]while[/color] ($r = [color=#0000FF]mysql_fetch_array[/color]($fin)) {
            [color=#00BF00]if[/color] ($r[[color=#FF0000]'resource'[/color]] [color=#0000FF]==[/color] $l) {
            [color=#0000FF]echo[/color] $r[[color=#FF0000]'blahblah'[/color]];
            }
        }
}   
?>
 
The other did the same as above but used two while loops... the first loop called each resource type and then the second would call the data....

If my explanation is a bit hard to understand, I want the code to have:

Code: Select all

Resource1:
>>list rows which belong to this resource>>
Resource2:
>>list rows...>
etc
edit:
at the moment that code puts out

Code: Select all

Resource1:
>>rows which belong to resource1>>
Resource2:
Resource3:
Resource4:
etc
It is executing the first foreach statement then the entire while statement and then going back and finishing the the foreach statement without executing the while statement again...
mickeyunderscore
Forum Contributor
Posts: 129
Joined: Sat Jan 31, 2009 9:00 am
Location: UK

Re: while loop within foreach/while loop....

Post by mickeyunderscore »

Are you using a GROUP BY on your MySQL query? If not, could you share the column/table names?
joel24
Forum Newbie
Posts: 10
Joined: Tue Feb 03, 2009 6:18 am

Re: while loop within foreach/while loop....

Post by joel24 »

Code: Select all

[color=#FFBF00]// retrive financials details for page viewing[/color]
        $sql = "SELECT * FROM financials";
        $fin = @mysql_query($sql);
        if (!$fin) {
            exit('<p>Error retrieving financials: ' . mysql_error() . '</p>');
        }
        
        [color=#FFBF00]// retrive resources for financial grouping[/color]
        $sql = "SELECT DISTINCT resource FROM financials ORDER BY resource";
        $resources = @mysql_query($sql);
        if (!$resources) {
            exit('<p>Error retrieving financials: ' . mysql_error() . '</p>');
        }
    
        while ($_res = mysql_fetch_array($resources)) {
            $res[] = $_res[0];
        }
then the next part of code is in the body.

Code: Select all

 
foreach ($res as $l) {
    [color=#FFBF00]//echo the heading[/color]
   echo "$l";
            [color=#FFBF00]//list row data within resource type[/color]
             while ($r = mysql_fetch_array($fin)) {
                   if ($r['resource'] == $l) {
                         echo $r['blah'];
                         echo $r['blahblah'];
                         etc etc
                   }
             }
}
mickeyunderscore
Forum Contributor
Posts: 129
Joined: Sat Jan 31, 2009 9:00 am
Location: UK

Re: while loop within foreach/while loop....

Post by mickeyunderscore »

You could cut it down into one query and one loop:

Code: Select all

$sql = "SELECT * FROM financials ORDER BY resource";
$fin = @mysql_query($sql);
if (!$fin) {
    exit('<p>Error retrieving financials: ' . mysql_error() . '</p>');
}
 
$last_resource = 'junk';
while ($_res = mysql_fetch_array($fin)) {
    if($_res['resource'] !== $last_resource){
        $last_resource = $_res['resource'];
        echo "<h1>$last_resource</h1>";
    }
    echo '<p>' . $_res['blahblahblah'] . '</p>';
}
joel24
Forum Newbie
Posts: 10
Joined: Tue Feb 03, 2009 6:18 am

Re: while loop within foreach/while loop....

Post by joel24 »

thank you!
i was staring at that for so long and didn't even think of that!
you're a life saver.
Post Reply