不能使用嵌套的while循环执行准备好的语句

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>';
    }
}

当连接中有行将从另一个语句中提取的行时，您不能prepare()语句。您必须首先关闭前一个结果集，或者从中取出所有行。

然而…

我不认为需要检索location 的外部查询，因为它没有WHERE子句。您正在选择所有位置，因此可以完全省略该部分。您使用外部循环的全部目的是为每个位置创建一个<h3>，这是极其浪费的(除了最初导致代码中断之外)

相反，执行一次查询并在fetch循环中检查位置是否已更改。当它改变时，输出标题

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

因此，完全删除外部查询和循环，并使用如下模式来检测location中的更改。请确保ORDER BY location，以便它们为您排序。

不需要绑定参数。您可以使用query()调用来完成此操作，因为位置不再是可变的，并且已知$day0是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>';

现在来看它失败的原因....当还有行需要从上一个语句或查询中提取时，不能prepare()一个新语句。您必须首先获取所有行，或者使用$stmt->close()关闭语句。所以你不能嵌套取回循环。

更好的方法是首先将所有行取出到数组中，然后遍历该数组:

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
}

通常，这可以通过适当的JOIN来解决。