dongyan5141 2013-01-14 12:49
浏览 59
已采纳

无法使用嵌套的while循环执行预准备语句

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>';
    }
}
  • 写回答

1条回答 默认 最新

  • dtu36380 2013-01-14 21:05
    关注

    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.

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题?

悬赏问题

  • ¥15 jupyterthemes 设置完毕后没有效果
  • ¥15 matlab图像高斯低通滤波
  • ¥15 针对曲面部件的制孔路径规划,大家有什么思路吗
  • ¥15 钢筋实图交点识别,机器视觉代码
  • ¥15 如何在Linux系统中,但是在window系统上idea里面可以正常运行?(相关搜索:jar包)
  • ¥50 400g qsfp 光模块iphy方案
  • ¥15 两块ADC0804用proteus仿真时,出现异常
  • ¥15 关于风控系统,如何去选择
  • ¥15 这款软件是什么?需要能满足我的需求
  • ¥15 SpringSecurityOauth2登陆前后request不一致