[RESOLVED] Nested Loop with Query Within, vs. Outside Loop

bronzino

I feel like I'm on the verge of a breakthrough here, but I can't find the answer to a question. Can someone please tell me why there is a difference here?

Please assume the following is planted between the body tags of my PHP page.

Why does this work:

$Classes = mysql_query('SELECT * FROM Shows_Classes');
while ($row = mysql_fetch_array($Classes)) {
  echo $row['ClassName'];
  $Judges = mysql_query("SELECT * FROM Shows_Judges");
    while ($row2 = mysql_fetch_array($Judges)) {
        echo $row2['PeopleID'];
  }
}

But this does not (the second, nested loop executes just once), when queries $Classes and $Judges are defined elsewhere (above the header)?

while ($row = mysql_fetch_array($Classes)) {
  echo $row['ClassName'];
  while ($row2 = mysql_fetch_array($Judges)) {
    echo $row2['PeopleID'];
  }
}

Sample Output from the first one:

Class1 123 456 789
Class2 123 456 789
Class3 123 456 789

Sample Output from the second one:

Class1 123 456 789
Class2
Classe

cahva

Because in the first one you initialize the query result again before the second while loop:

$Judges = mysql_query("SELECT * FROM Shows_Judges");

.. which "sets" the pointer back to beginning everytime the first while is iterated. In the last example the second while clause has iterated already all the results so the "while ($row2 = mysql_fetch_array($Judges))" will not return anything unless you reset the query result or move the pointer to beginning.

PS. Oh on the sidenote your second example does not make sense if you are not using the first results row somehow in the second query because as it is, its just looping the same results all over again. So in that context, the second example is kind of stupid as you most definately want to create the second query based on the first one. Or even better, create only one query and make use of joins in sql.

bronzino

Thanks for your response - I get it.

The second query is a dropdown menu that displays the assigned judge for a given class. If the user wants to change the assigned judge, she need only manipulate that menu and "submit." To give her the option of selecting from all available judges, I use the second query. I wouldn't have guessed that a join would make sense for this purpose.