php - 构建动态查询时绑定(bind)变量的数量与标记错误的数量不匹配

Question

我不断收到以下错误:

Warning: PDOStatement::execute(): SQLSTATE[HY093]: Invalid parameter number: number of boundvariables does not match number of tokens

尝试构建动态 SQL 查询时。知道损坏在哪里吗？我已经搜索了几个小时，但仍然没有成功

$park = $_POST["park"];
$lecturestyle_id = $_POST["lecturestyle"];
$group_size = $_POST["groupsize"];
$roomstructure_id = $_POST["roomstructure"];
$array = explode(",", $_POST["facilities"]);

// change Mon here

echo '<td class="gridSide">
Mon
';

// build facilities search

for($i = 0; $i < count($array); $i++){  
    if ($array[$i]!=0) {
    $fac .= ' AND facilities_id='.$array[$i].'';
    }
    else
    $fac .= '';
}

echo '</td>';


for ($i = 1; $i <= 9; $i++) 
{

        // change mon here

        echo '<td class="box" id="mon'.$i.'">';

        // dynamically build sql query

        $sql = 
        "
        SELECT DISTINCT COUNT(*) FROM ts_room rm
        LEFT JOIN ts_roomfacilities rf
        ON rm.id = rf.room_id
        LEFT join ts_facilities f
        ON f.id = rf.facilities_id
        LEFT JOIN ts_building b
        ON rm.building_id=b.id
        WHERE capacity>=:group_size";

        $sql .= $fac;

        if($park!="Any") {
        $sql .= " AND b.park_id=:park";
        }

        if($lecturestyle_id!="Any") {
        $sql .= " AND lecturestyle_id=:lecturestyle_id";
        }

        if($roomstructure_id!="Any") {
        $sql .= " AND roomstructure_id=:roomstructure_id";
        }

        $sql .= " AND rm.id NOT IN
        (SELECT COUNT(*)
         FROM ts_request rq
         LEFT JOIN ts_allocation a ON a.request_id = rq.id
         WHERE 
         day_id=1 AND period_id=:period
         OR a.status IS NOT NULL
         AND a.status IN ('Pending','Declined','Failed'))";

         $stm = $pdo->prepare( $sql );
         $stm->execute( array( ':roomstructure_id' => $roomstructure_id, ':lecturestyle_id' => $lecturestyle_id, 
         ':group_size' => $group_size, ':park' => $park, ':period' => $i));
         $rows = $stm->fetchColumn();

         echo $rows.'<br>free</td>';            
         echo '</td>';

}

Answer 1

当您执行 $stm->execute 时，您为 ':roomstructure_id'、':lecturestyle_id'、':group_size'、':park' 和 ':period'，无论它们实际上是否存在于查询，即它们中的任何一个是否等于 'Any'。

您应该做的是在构建查询时构建值数组...只要字段的搜索值不是 'Any' 就向其添加值.

例如

if($park!="Any") {
    $sql .= " AND b.park_id=:park";
    $searchArray[':park'] = $park;
}