2

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?

Jenz
  • 8,280
  • 7
  • 44
  • 77
Parveez Ahmed
  • 1,325
  • 4
  • 17
  • 28
  • First instinct tells me that within the while loop, your database handle still needs to access the original query. What happens if you create two connections, I.e. declare `$mysqli2 = new mysqli(...);` and then use that object to prepare your inner query? – Alex Jul 09 '14 at 06:08
  • Did you try to show `mysqli` errors? Add `mysqli_error($mysqli)` after the second `execute()` – Oscar Pérez Jul 09 '14 at 06:11
  • Stick `mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);` at the very top of your script. Also, make sure you have enabled `display_errors` and set `error_reporting` to `E_ALL` in your `php.ini` file – Phil Jul 09 '14 at 07:09
  • using $stmt1->store_result() solved my problem, could anyone tell me what actually $stmt1->store_result() does ? – Parveez Ahmed Jul 09 '14 at 08:36

1 Answers1

0

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();

 ?>
Logan Wayne
  • 6,001
  • 16
  • 31
  • 49