Nested While loop only runs inside loop correctly

Question

I've got two while loops that I'm using to run through some MySQL data.

The first while loop runs through a bunch of reports and checks the 'state' (as in United States) that is stored in the table.

Within that loop, I'm checking a second table with a while loop for 'state' as well and if the states match then I push some info to an array. I got rid of the other code for simplicity's sake here.

Basically, I started with the first while loop and logged all the info to the console and it successfully looped through all the entries in the table.

I then put the nested while loop in there and when I try to alert or log it to the console, it's only looped through the instances of the first entry in the parent loop.

What I mean is that in the $row_wind loop, the first entry is CT (Connecticut). When I log out the parent loop, it logs everything in that table (with CT being the first one). When I use the below code it's logging just CT and then all the entries in the child loop but doesn't counter the parent loop after that. Am I not able to nest while loops?

while($row_wind = mysqli_fetch_array($sql_wind_result)){
    while($row_alert = mysqli_fetch_array($result_user_alerts)){
        echo "<script>alert('".$row_wind['state']." ".$row_alert['state']."');</script>";
    }
}

cincodenada · Accepted Answer · 2013-07-12 18:50:33Z

1

mysqli_fetch_array is similar to array_pop() in that once it fetches a result, it's not going to fetch that result again.

So my guess is that your $row_wind loop is actually running through just fine, but the second time through the $row_alert loop looks for more results, finds that it's already run through them (in the loop where $row_wind = 'CT').

If you put some debug info inside the $row_wind loop but outside the $row_alert loop you should see the difference.

To fix this, you'll either have to re-run the $result_user_alerts query before the inner loop, or - probably better if the inner query isn't dependent on the state - run mysqli_fetch_all() beforehand to fetch all your results for the inner loop up front, and then loop through the resulting array with your inner loop with a foreach() or while(). That way you only have to fetch them once.

If you don't have mysqli_fetch_all() avaialble, you can just manually loop through:

$user_alerts = array();
while($row_alert = mysqli_fetch_array($result_user_alerts)){
    $user_alerts[] = $row_alert;
}

A cautionary note: The way this is structured, however, seems like it could be better-executed with some better queries - I don't know the details of your application, but when you're matching values from two SQL queries in a PHP loop, that matching can usually be done more quickly and efficiently by the database.

edited Jul 12, 2013 at 18:50

answered Jul 11, 2013 at 23:55

cincodenada

3,13929 silver badges36 bronze badges

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

5 Comments

MillerMedia Over a year ago

That makes total sense. I never thought of it that way. I'll look into a separate solution, that gets me pointed in the right direction. Thank you!

MillerMedia Over a year ago

mysqli_fetch_all() doesn't seem to be valid anymore. Are there any other functions like that that will store all the info into an array?

cincodenada Over a year ago

I use PDO myself, but it does look like you have to have some other stuff set up to use fetch_all. You can still just loop through it and build up an array manually. I added an example of that in my answer.

MillerMedia Over a year ago

I also found out that if I make a new SQL query from within the parent while loop but outside of the nested loop, it'll run correctly. I'm assuming this is because it's a brand new fetch_array on a new query and it works. That works for what I'm doing right now because it's not a huge amount of data that will be queried every time it runs.

cincodenada Over a year ago

Yeah, the new sql query will of course re-fetch the rows and repopulate the pool of queries. I mentioned that in my answer as well, but don't suggest it because of the load on the db.

Collectives™ on Stack Overflow

Nested While loop only runs inside loop correctly

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related