Page 1 of 1

while loop within foreach/while loop....

Posted: Tue Feb 03, 2009 6:35 am
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...

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

Posted: Tue Feb 03, 2009 6:49 am
by mickeyunderscore
Are you using a GROUP BY on your MySQL query? If not, could you share the column/table names?

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

Posted: Tue Feb 03, 2009 7:01 am
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
                   }
             }
}

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

Posted: Tue Feb 03, 2009 7:23 am
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>';
}

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

Posted: Tue Feb 03, 2009 7:36 am
by joel24
thank you!
i was staring at that for so long and didn't even think of that!
you're a life saver.