CODEWITHSUNDEEP
while-loopmysqli
AlanPHP
AlanPHP

Reputation: 188

Mysqli looping with query inside

$dungeon = 0;
if($stmt->prepare("SELECT id,name FROM `dungeons` WHERE id > ?")) {
$stmt->bind_param('i',$dungeon);
$stmt->execute();
$stmt->store_result();
$stmt->bind_result($id,$name);
$stmt2=$stmt;
while($stmt->fetch()) {
    echo "<th>".$name."</th>";
    $query = "SELECT COUNT(id),AVG(dungeon_percent),MAX(dungeon_percent) FROM `users` WHERE dungeon_current = ?"; $what=$id;
    $stmt2->prepare($query);
    $stmt2->bind_param('i',$what);
    $stmt2->execute();
    $stmt2->store_result();
    $stmt2->bind_result($howmany,$avg,$max);
    $stmt2->fetch(); 
    echo "<td align='center'>".$howmany."</td><td align='center'>".round($avg)."%</td><td align='center'>".$max."%</td></tr>";
    }
}

Is what I have. Yet it only loops one time when there are actually 4 rows? Thanks.

Upvotes: 0

Views: 557

Answers (1)

Cyclonecode
Cyclonecode

Reputation: 29991

Have you tried creating a new statement like:

 while($stmt->fetch()) {
      $query = "SELECT COUNT(id),AVG(dungeon_percent),MAX(dungeon_percent) 
                FROM `users`
                WHERE dungeon_current = ?"; 
      $what=$id;
      // prepare your new statement using the connection
      $stmt2 = $your_db->prepare($query);
      $stmt2->bind_param('i',$what);
      $stmt2->execute();
      $stmt2->store_result();
      $stmt2->bind_result($howmany,$avg,$max);
      $stmt2->fetch(); 
      echo "<td align='center'>".$howmany.
           "</td><td align='center'>".round($avg).
          "%</td><td align='center'>".$max."%</td></tr>";
  }

I think a good idea would be to prepare the second query outside of the loop. You should also be able to call bind_param() and bind_result() outside the loop and then simply execute it multiple times.

This is a more efficient way of achiving the same thing:

 $query = "SELECT COUNT(id), AVG(dungeon_percent), MAX(dungeon_percent) 
           FROM `users`
           WHERE dungeon_current = ?"; 

 $stmt2 = $your_db->prepare($query);
 $stmt2->bind_param('i', $id);
 $stmt2->bind_result($howmany, $avg, $max);

 while($stmt->fetch()) {
      $stmt2->execute();
      $stmt2->store_result();
      $stmt2->fetch(); 
      echo "<td align='center'>".$howmany.
           "</td><td align='center'>".round($avg).
          "%</td><td align='center'>".$max."%</td></tr>";
  }

Upvotes: 1

Related Questions