PHP PDO/事务更新状态产生错误 (SQLSTATE[HY093] : Invalid parameter number: parameter was not defined)

Question

这个问题已经有答案了:

Error when preparing a multiple insert query (5 个回答)

已关闭 4 年前。

在下面的函数中我有两个更新语句。第一个执行正常，但第二个执行不正常，并产生错误(参数号无效)。 Threshold为 float (例如0.55，最多两位数)，Likelihood、Consequence为整数)。

如何更正我的代码以免产生此错误？

public function updateAll($params = [])
{
    $sql = "update
            risklevels
            set
                riskmaximum = :riskmaximum,
                riskhigh = :riskhigh,
                riskmedium = :riskmedium,
                riskminimum = :riskminimum
            where risklevelid = 1";

    $sql2 = "update
             riskmatrixthresholds
             set 
                level = :level
             where likelihood = :likelhood
             and consequence = :consequence";
    try
    {    
        $this->db->beginTransaction();  
        $statement = $this->db->prepare($sql);
        $statement->bindValue(':riskmaximum', $params['Levels']['riskmaximum']);
        $statement->bindValue(':riskhigh' , $params['Levels']['riskhigh']);
        $statement->bindValue(':riskmedium' , $params['Levels']['riskmedium']);
        $statement->bindValue(':riskminimum' , $params['Levels']['riskminimum']);

        $statement->execute(); 

        for ($l = 1; $l <= 5; $l++)
        {
            for($c = 1; $c <= 5; $c++)
            {
                $threshold = $params['Thresholds'][$l][$c];
                $statement2 = $this->db->prepare($sql2);  
                $statement2->bindValue(':level', $threshold);
                $statement2->bindValue(':likelihood', $l);
                $statement2->bindValue(':consequence', $c);
                $statement2->execute();
            }
        }
        $this->db->commit();

        return ["Succeeded" => true, "Result" => "Risk Configuration Updated!"];
    }
    catch (\PDOException $e)
    {
        return ["Succeeded" => false, "Result" => $e->getMessage()];
    }
}

Answer 1

您的一个 SQL 变量拼写错误。

将 wherelikelihood = :likelihood 更改为 wherelikelihood = :likelihood