looping

loops database

[immatt]

Im kind of new at php and I just have the hardest time with looping. I have a database that I want to put into separate HTML tables. If my database contained

Table: game
id___level___stage___name
1______1_______2_____Jim
2______3_______1_____Mark
3______3_______4_____Jane
4______2_______1_____Joyce
5______1_______1_____Kim
6______1_______1_____Matt
7______3_______2_____Mike
8______1_______3_____Steve

What I would like is for it to end up looking like this:

Level 1 - Stage 1

Kim
Matt

Level 1 - Stage 2

Jim

Level 1 - Stage 3

Steve

Level 1 - Stage 4

Level 2 - Stage 1

Joyce

Level 2 - Stage 2

Level 2 - Stage 3

Level 2 - Stage 4

Level 3 - Stage 1

Mark

Level 3 - Stage 2

Mike

Level 3 - Stage 3

Level 3 - Stage 4

Jane

I have this but it doesn't work that well.

$i=1;
$result = mysql_query("SELECT * FROM game ORDER BY level,stage");
while ($row = mysql_fetch_array($result)) {
$level=$row['level'];
$stage= $row['stage'];
$name= $row['name'];
if($episode==$i){
print 'Level '.$level.' - Stage '.$stage'<br />';
$i++;}
print $name'<br />';
}

This does well for the first couple lines but then it stops printing the levels. For the life of me I cannot figure this out. Any help would be great.
Thank you
Matt

[bkeep]

how does the episode work in your query? if($episode==$i){

What happens is $i is not equal to the $episode and the code inside the if statement is not executed

[immatt]

that was my mistake, it is supposed to be
if($level==$i){

It still doesn't work properly though.

[bkeep]

what happens is each iteration of the while loop increments the $i variable by one so the first pass $i == 1 the results for one row are displayed so on the second pass $i == 2 and so on and so forth.

you could try something like this and see what happens. I am not a MySQL guru and I would bet this could be done easier with some kind of join in one query but this should get you thinking about alternate solutions atleast.

$result = mysql_query("SELECT name FROM game GROUP BY name");
while ($row = mysql_fetch_array($result)) {
$sub_result = mysql_query("SELECT level,stage FROM game WHERE name = $row[name] ORDER BY level,stage");
while ($sub_row = mysql_fetch_array($sub_result)) {
print "Level $sub_row[level] - Stage $sub_row[stage]<br />";
}
print "$row[name]<br />";
}

[idfer]

immatt, you're on the right track with your original code, but you don't want to increment $i, instead you want to assign the current level to it (and use a better variable name :)):

$curLevel = ''; 
$curStage = ''; 
$result = mysql_query("SELECT * FROM game ORDER BY level,stage"); 
while ($row = mysql_fetch_array($result)) { 
 $level=$row['level']; 
 $stage= $row['stage']; 
 $name= $row['name']; 
 if($level != $curLevel ¦¦ $stage != $curStage){ 
  print 'Level '.$level.' - Stage '.$stage'<br />'; 
  $curLevel = $level;
  $curStage = $stage
 } 
 print $name'<br />'; 
}

The one thing that won't give you is headers for level/stages that have no players. If you got the complete list of levels and stages in another table, you can outer join your game table to it, or if all levels have 4 stages you can add a loop inside the if statement to print the intervening headers:

...
 if($level != $curLevel ¦¦ $stage != $curStage){ 
  // Output missing headers up to the new level.
  while($curLevel < $level) {
   while($curStage < 4) {
    $curStage++;
    print 'Level '.$curLevel.' - Stage '.$curStage'<br />'; 
   }
   $curLevel++;
   $curStage = 0;
  }
  // Output missing headers for new level up to the new stage.
  while($curStage < $stage) {
   $curStage++;
   print 'Level '.$curLevel.' - Stage '.$curStage'<br />'; 
  }
  // Output header for new level and stage.
  print 'Level '.$level.' - Stage '.$stage'<br />'; 
 } 
...

I think that'll work, hope it makes sense.

[immatt]

Thank you so much for all of the help!