mysql - 使用bindParam的pdo更新显示绑定(bind)变量数量与 token 数量不匹配的错误

Question

我有一个简单的表单用于将图像名称更新到 mysql 数据库中，但不幸的是它总是抛出错误 SQLSTATE[HY093]: 无效的参数编号:绑定(bind)变量的数量与标记的数量不匹配。代码看起来没问题，但我不知道它不能更新数据库。任何帮助表示赞赏。这是我打印出来的图像名称和 SQL。

Array
(
    [0] => 20141027103534_0.jpg
    [1] => 20141027103534_1.jpg
    [2] => 
    [3] => 
    [4] => 
    [5] => 20141027103534_5.jpg
    [6] => 20141027103534_6.jpg
    [7] => 
    [8] => 
    [9] => 
)
        UPDATE `image`
            SET 
                `propertyPictureCate1` = :propertyPictureCate1,
                `propertyPictureCate2` = :propertyPictureCate2,
                `propertyPictureCate3` = :propertyPictureCate3,
                `propertyPictureCate4` = :propertyPictureCate4,
                `propertyPictureCate5` = :propertyPictureCate5,
                `memo1` = :memo1,
                `memo2` = :memo2,
                `memo3` = :memo3,
                `memo4` = :memo4,
                `memo5` = :memo5,
                `spotPictureCate1` = :spotPictureCate1,
                `spotPictureCate2` = :spotPictureCate2,
                `spotPictureCate3` = :spotPictureCate3,
                `spotPictureCate4` = :spotPictureCate4,
                `spotPictureCate5` = :spotPictureCate5,
                `spotName1` = :spotName1,
                `spotName2` = :spotName2,
                `spotName3` = :spotName3,
                `spotName4` = :spotName4,
                `spotName5` = :spotName5,
                `distance1` = :distance1,
                `distance2` = :distance2,
                `distance3` = :distance3,
                `distance4` = :distance4,
                `distance5` = :distance5,
`img1` = :img1,
`img2` = :img2,
`img6` = :img6,
`img7` = :img7 
WHERE `buildingID` = :buildingID

我测试打印出来的bindParam 中的图像名称。

img1 20141027103534_0.jpg
img2 20141027103534_1.jpg
img6 20141027103534_5.jpg
img7 20141027103534_6.jpg

下面是我的上述结果的代码

if($_SERVER['REQUEST_METHOD'] == 'POST') {

    $file_name = [];
    for ($i = 0; $i < count($_FILES['file']['tmp_name']); $i++) {
        if($_FILES['file']['error'][$i] != 4) {
            $validextensions = array("jpeg", "jpg", "png","gif");
            $ext = explode('.', basename($_FILES['file']['name'][$i]));
            $file_extension = end($ext);
            $file_name[$i] = date("Ymdhis") . "_". $i . "." . $ext[count($ext) - 1];
            $target_path = $_SERVER['DOCUMENT_ROOT'] . "/".PROJ_DIR."/uploads/" . date("Ymdhis") . "_". $i . "." . $ext[count($ext) - 1];
            move_uploaded_file($_FILES['file']['tmp_name'][$i], $target_path);
        } else {
            $file_name[$i] = "";
        }
    }

    try {
        $sql = "
        UPDATE `image`
            SET 
                `propertyPictureCate1` = :propertyPictureCate1,
                `propertyPictureCate2` = :propertyPictureCate2,
                `propertyPictureCate3` = :propertyPictureCate3,
                `propertyPictureCate4` = :propertyPictureCate4,
                `propertyPictureCate5` = :propertyPictureCate5,
                `memo1` = :memo1,
                `memo2` = :memo2,
                `memo3` = :memo3,
                `memo4` = :memo4,
                `memo5` = :memo5,
                `spotPictureCate1` = :spotPictureCate1,
                `spotPictureCate2` = :spotPictureCate2,
                `spotPictureCate3` = :spotPictureCate3,
                `spotPictureCate4` = :spotPictureCate4,
                `spotPictureCate5` = :spotPictureCate5,
                `spotName1` = :spotName1,
                `spotName2` = :spotName2,
                `spotName3` = :spotName3,
                `spotName4` = :spotName4,
                `spotName5` = :spotName5,
                `distance1` = :distance1,
                `distance2` = :distance2,
                `distance3` = :distance3,
                `distance4` = :distance4,
                `distance5` = :distance5".
                $s1 = (($file_name[0] != "") ? ",\n`img1` = :img1" : NULL).
                $s2 = (($file_name[1] != "") ? ",\n`img2` = :img2" : NULL).
                $s3 = (($file_name[2] != "") ? ",\n`img3` = :img3" : NULL).
                $s4 = (($file_name[3] != "") ? ",\n`img4` = :img4" : NULL).
                $s5 = (($file_name[4] != "") ? ",\n`img5` = :img5" : NULL).
                $s6 = (($file_name[5] != "") ? ",\n`img6` = :img6" : NULL).
                $s7 = (($file_name[6] != "") ? ",\n`img7` = :img7" : NULL).
                $s8 = (($file_name[7] != "") ? ",\n`img8` = :img8" : NULL).
                $s9 = (($file_name[8] != "") ? ",\n`img9` = :img9" : NULL).
                $s10 = (($file_name[9] != "") ? ",\n`img10` = :img10" : NULL).
            " \nWHERE `buildingID` = :buildingID
            ";
            echo '<pre>';
            print_r($file_name);
            echo '</pre>';
            echo '<pre>';
            print_r($sql);
            echo '</pre>';
        $stmt = $con->prepare($sql);

        $stmt->bindParam(":propertyPictureCate1",$_POST['propertyPictureCate1']);
        $stmt->bindParam(":propertyPictureCate2",$_POST['propertyPictureCate2']);
        $stmt->bindParam(":propertyPictureCate3",$_POST['propertyPictureCate3']);
        $stmt->bindParam(":propertyPictureCate4",$_POST['propertyPictureCate4']);
        $stmt->bindParam(":propertyPictureCate5",$_POST['propertyPictureCate5']);
        $stmt->bindParam(":memo1",$_POST['memo1']);
        $stmt->bindParam(":memo2",$_POST['memo2']);
        $stmt->bindParam(":memo3",$_POST['memo3']);
        $stmt->bindParam(":memo4",$_POST['memo4']);
        $stmt->bindParam(":memo5",$_POST['memo5']);
        $stmt->bindParam(":spotPictureCate1",$_POST['spotPictureCate1']);
        $stmt->bindParam(":spotPictureCate2",$_POST['spotPictureCate2']);
        $stmt->bindParam(":spotPictureCate3",$_POST['spotPictureCate3']);
        $stmt->bindParam(":spotPictureCate4",$_POST['spotPictureCate4']);
        $stmt->bindParam(":spotPictureCate5",$_POST['spotPictureCate5']);
        $stmt->bindParam(":spotName1",$_POST['spotName1']);
        $stmt->bindParam(":spotName2",$_POST['spotName2']);
        $stmt->bindParam(":spotName3",$_POST['spotName3']);
        $stmt->bindParam(":spotName4",$_POST['spotName4']);
        $stmt->bindParam(":spotName5",$_POST['spotName5']);

        $stmt->bindParam(":buildingID",$_POST['buildingID']);

        if($file_name[0] !== ""){
            echo 'img1 '.$file_name[0].'<br>';
            $stmt->bindParam(":img1", $file_name[0]);
        }
        if($file_name[1] !== ""){
            echo 'img2 '.$file_name[1].'<br>';
            $stmt->bindParam(":img2", $file_name[1]);
        }
        if($file_name[2] !== ""){
            echo 'img3 '.$file_name[2].'<br>';
            $stmt->bindParam(":img3", $file_name[2]);
        }
        if($file_name[3] !== ""){
            echo 'img4 '.$file_name[3].'<br>';
            $stmt->bindParam(":img4", $file_name[3]);
        }
        if($file_name[4] !== ""){
            echo 'img5 '.$file_name[4].'<br>';
            $stmt->bindParam(":img5", $file_name[4]);
        }
        if($file_name[5] !== ""){
            echo 'img6 '.$file_name[5].'<br>';
            $stmt->bindParam(":img6", $file_name[5]);
        }
        if($file_name[6] !== ""){
            echo 'img7 '.$file_name[6].'<br>';
            $stmt->bindParam(":img7", $file_name[6]);
        }
        if($file_name[7] !== ""){
            echo 'img8 '.$file_name[7].'<br>';
            $stmt->bindParam(":img8", $file_name[7]);
        }
        if($file_name[8] !== ""){
            echo 'img9 '.$file_name[8].'<br>';
            $stmt->bindParam(":img9", $file_name[8]);
        }
        if($file_name[9] !== ""){
            echo 'img10 '.$file_name[9].'<br>';
            $stmt->bindParam(":img10", $file_name[9]);
        }
        //$stmt->debugDumpParams();
        $stmt->execute();


        $con = null;
    } catch(PDOException $e) {
        echo $e->getMessage();
    }
}

Answer 1

您忘记将距离参数绑定(bind)到您的语句。

在调用 $stmt->execute() 之前，您应该添加这些行以绑定(bind)到 $stmt

$stmt->bindParam(":distance1",$_POST['distance1']);
$stmt->bindParam(":distance2",$_POST['distance2']);
$stmt->bindParam(":distance3",$_POST['distance3']);
$stmt->bindParam(":distance4",$_POST['distance4']);
$stmt->bindParam(":distance5",$_POST['distance5']);

或从 $sql 字符串变量中删除这些行

`distance1` = :distance1,
`distance2` = :distance2,
`distance3` = :distance3,
`distance4` = :distance4,
`distance5` = :distance5