CODEWITHSUNDEEP
phpmysqlmysqli
Steve
Steve

Reputation: 596

Can't execute prepared statements using nested while loops

I am having trouble with changing over some code from old style mysql queries to being prepared. I assume the problem is due to the fact that I'm using multiple whiles which each have their own query in which is causing problems cause only one prepared statement can be active at a time.

EDIT: If anyone cares, I've made it work with only 2 loops like so - 

function createDeskMenu()
{
    global $bookingTimes, $dbconn;
    $day0 = mktime(0, 0, 0, date("m")  , date("d"), date("Y"));

    $query = "SELECT location FROM location";
    $result = mysqli_query($dbconn,$query);
    mysqli_num_rows($result);
    while ($row = mysqli_fetch_array($result))
    {
        $location = $row['location'];
        echo "<h3>$location</h3><div>";
        $query = $dbconn -> prepare("SELECT COALESCE( CountDesk, 0 ) total, name, d.desk_id, phone, fax, dock, pc FROM desk d LEFT JOIN (SELECT COUNT(booked.desk_id) CountDesk, desk_id FROM booked WHERE booking_id >=?)b ON d.desk_id = b.desk_id WHERE location=?");
        $query->bind_param("is",$day0, $location);
        $query->execute();
        $query->bind_result($totalCount,$name,$desk_id,$phone,$fax,$dock,$pc);
        while($query->fetch()) {
            $total = count($bookingTimes) * 14 - $totalCount;
            echo '<a href="?page=desk&desk='.$desk_id.'"><div class="desk"><b>'.$name.' 
            ('.$total.' Available Bookings)</b><li>Facilities:';
            if($phone){echo " Phone,";}if($fax){echo " Fax Machine,";}if($dock){echo " Laptop Dock.";}if($pc){echo " Desktop Workstation.";}
            echo '</li></div></a><hr />';
        }
        $query->close();
        echo '</div>';
    }
}

Upvotes: 1

Views: 1129

Answers (1)

Michael Berkowski
Michael Berkowski

Reputation: 270647

You cannot prepare() a statement while the connection has rows waiting to be fetched from another statement. You must first either close the previous result set or fetch all rows from it.

However...

I don't see the need for the outer query which retrieves location at all, as it has no WHERE clause. You are selecting all locations, and can therefore omit that part entirely. All you are using the outer loop for is to create a <h3> for each location, and this is extremely wasteful ( in addition to originally causing you breakage in the code)

Instead, do one query and in the fetch loop, check if the location has changed. When it changes, output your header

echo "<h3>$location</h3><div>";

So remove the outer query and loop entirely, and use a pattern like the following to detect changes in location. Make sure to ORDER BY location so they are sorted for you.

No bound parameters are needed. You can do this with a query() call since the location is no longer variable and $day0 is known to be a timestamp from mktime().

// Substitute a query() call and $day0 can be inserted directly.
// This one query fetches all locations sorted...
$query = $dbconn->query("
  SELECT
   COALESCE( CountDesk, 0 ) total,
   name,
   d.desk_id,
   phone,
   fax,
   dock,
   pc
 FROM 
   desk d
   LEFT JOIN (
     SELECT COUNT(booked.desk_id) CountDesk, desk_id FROM booked WHERE booking_id >= $day0
   )b ON d.desk_id = b.desk_id 
 ORDER BY location");

// Store the last location in a variable which starts empty...
$location = "";

while($row = $query->fetch_assoc()) {
  // on change of $location, update the variable.
  if ($location !== $row['location']) {
    $location = $row['location'];
    // And output the new location value
     echo "<h3>$location</h3><div>";
  }

  // Do the rest of your loop.
  $total = count($bookingTimes) * 14 - $row['total'];
  echo '<a href="?page=desk&desk='.$row['desk_id'].'"><div class="desk"><b>'.$row['name'].' 
  ('.$total.' Available Bookings)</b><li>Facilities:';
  if($row['phone']){
    echo " Phone,";
  }
  if($row['fax']){ 
    echo " Fax Machine,";
  }
  if($row['dock']){
    echo " Laptop Dock.";
  }
  if($row['pc']){
    echo " Desktop Workstation.";
  }
  echo '</li></div></a><hr />';
}
$row->close();
echo '</div>';

Now on to the reason it was failing.... You cannot prepare() a new statement while there are rows remaining to be fetched from a previous statement or query. You must first either fetch all the rows, or close the statement with $stmt->close(). So effectively you cannot nest fetch loops.

The better method is to first fetch all rows into an array and then loop over that array:

while ($row = $first_query->fetch()) {
  // Append all onto an array
  $first_query_rows[] = $row;
}
// Then loop over that
foreach ($first_query_rows as $row) {
  // Do a new query with $row
}

Usually though, this can be solved with a proper JOIN.

Upvotes: 1

Related Questions