Nesting of MySQLi prepared statements

Question

I have two prepared statements (for example) where the second statement is to be executed within the while(stmt1->fetch()){} loop. But, the inner statement (stmt2) doesn't execute within the first while loop:

<?php
  $mysqli = new mysqli("localhost","root","","test");

  if(mysqli_connect_errno())
  {
    printf("connection failed: %s\n",mysqli_connect_error());
    exit();
  }

  $stmt1 = $mysqli->prepare("select id from posts");
  $stmt2 = $mysqli->prepare("select username from members where id=?"); 

  $stmt1->execute(); 
  $stmt1->bind_result($ID);

   while($stmt1->fetch())
   {
     echo $ID.' ';

      /*Inner query*/
      $stmt2->bind_param('i',$id);

      $id =$ID;    

      $stmt2->execute();

      $stmt2->bind_result($username);

      while($stmt2->fetch())
      {
        echo 'Username: '.$username;
      }     
      /*Inner query ends*/
   }  

 ?>

If I cut-paste the inner query part outside the outer while loop, it executes,but it is useless. What should I do to execute it properly?

Answer 1

Why do nested loop on it, when you can do INNER JOIN instead.

<?php

  $mysqli = new mysqli("localhost","root","","test");

  if(mysqli_connect_errno())
  {
    printf("connection failed: %s\n",mysqli_connect_error());
    exit();
  }

  if($stmt1 = $mysqli->prepare("SELECT posts.id,members.username FROM posts INNER JOIN members ON posts.id=members.id")){

    $stmt1->execute(); 
    $stmt1->bind_result($id,$username);

    while($stmt1->fetch()){

      printf("ID # %d.<br>Username: %s<br><br>",$id,$username);

    }    
    $stmt1->close(); 
  }

  $mysqli->close();

 ?>