PHP PDO/Transaction Update Statmement produces error (SQLSTATE[HY093]: Invalid parameter number: parameter was not defined)

Question

In the following function I have two update statements. The first one executes fine but the second one does not, producing an error (Invalid parameter number). Threshold is float (0.55 for example, with at most two digits) while Likelihood, and Consequence are integers).

How do I correct my code to not produce this error?

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()];
    }
}

You have a typo, the parameter in your query is called `:likelhood` instead of `:likelihood` — rickdenhaan, May 21 '19 at 19:23
This error happens when you use a variable but dont define a value for it. In this case `:likelhood` isn't defined but `:likelihood` is — Nicholas Summers, May 21 '19 at 19:23

score 2 · Accepted Answer · answered May 21 '19 at 19:26

2

You misspelled one of your SQL variables.

Change where likelihood = :likelhood to where likelihood = :likelihood

answered May 21 '19 at 19:26

Nicholas Summers

4,444
4
19
35

PHP PDO/Transaction Update Statmement produces error (SQLSTATE[HY093]: Invalid parameter number: parameter was not defined)

1 Answers1