A new error Invalid parameter number: number of bound variables does not match number of tokens

Question

I am running an update script and I'm getting this error:

SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens.

Below is my code: class.update.php

public function update($firstname, $lastname, $postaladdress, $mobilephone, $officephone, $personalemail, $officialemail, $nationality, $city, $identity, $id_number, $gender, $dob)
{
    try
    {
        $stmt=$this->db->prepare
        ("UPDATE clients SET
                 firstname      =:firsname, 
                 lastname       =:lastname, 
                 postaladdress  =:postaladdress, 
                 mobilephone    =:mobilephone,
                 officephone    =:officephone,
                 personalemail  =:personalemail,
                 officialemail  =:officialemail,
                 nationality    =:nationality,
                 city           =:city,
                 identity       =:identiy,
                 id_number      =:id_number,
                 gender         =:gender,
                 dob            =:dob
          WHERE client_id=:id");

        $stmt->bindparam(":firstname",$firstname);
        $stmt->bindparam(":lastname",$lastname);
        $stmt->bindparam(":postaladdress",$postaladdress);
        $stmt->bindparam(":mobilephone",$mobilephone);
        $stmt->bindparam(":officephone",$officephone);
        $stmt->bindparam(":personalemail",$personalemail);
        $stmt->bindparam(":officialemail",$officialemail);
        $stmt->bindparam(":nationality",$nationality);
        $stmt->bindparam(":city",$city);
        $stmt->bindparam(":identity",$identity);
        $stmt->bindparam(":id_number",$id_number);
        $stmt->bindparam(":gender",$gender);
        $stmt->execute();

        return true;
    }
    catch(PDOException $e)
    {
        echo $e->getMessage();  
        return false;
    }
}

Possible duplicate of [SQLSTATE\[HY093\]: Invalid parameter number: parameter was not defined](https://stackoverflow.com/questions/10966251/sqlstatehy093-invalid-parameter-number-parameter-was-not-defined) — Lucarnosky, Jun 01 '18 at 13:00

score 0 · Accepted Answer · 2018-06-04T07:13:45.853

You are missing:

$stmt->bindparam(":dob",$dob);
$stmt->bindparam(":id",$id);

And possibly an $id parameter in your function:

public function update($firstname, $lastname, etc etc etc, $id)

The full code should then be (assuming $id is a parameter for update()):

public function update($firstname, $lastname, $postaladdress, $mobilephone, $officephone, $personalemail, $officialemail, $nationality, $city, $identity, $id_number, $gender, $dob, $id)
{
    try
    {
        $stmt=$this->db->prepare
        ("UPDATE clients SET
                 firstname      =:firstname, 
                 lastname       =:lastname, 
                 postaladdress  =:postaladdress, 
                 mobilephone    =:mobilephone,
                 officephone    =:officephone,
                 personalemail  =:personalemail,
                 officialemail  =:officialemail,
                 nationality    =:nationality,
                 city           =:city,
                 identity       =:identity,
                 id_number      =:id_number,
                 gender         =:gender,
                 dob            =:dob
          WHERE client_id=:id");

        $stmt->bindparam(":firstname",$firstname);
        $stmt->bindparam(":lastname",$lastname);
        $stmt->bindparam(":postaladdress",$postaladdress);
        $stmt->bindparam(":mobilephone",$mobilephone);
        $stmt->bindparam(":officephone",$officephone);
        $stmt->bindparam(":personalemail",$personalemail);
        $stmt->bindparam(":officialemail",$officialemail);
        $stmt->bindparam(":nationality",$nationality);
        $stmt->bindparam(":city",$city);
        $stmt->bindparam(":identity",$identity);
        $stmt->bindparam(":id_number",$id_number);
        $stmt->bindparam(":gender",$gender);
        $stmt->bindparam(":dob",$dob);
        $stmt->bindparam(":id",$id);
        $stmt->execute();

        return true;
    }
    catch(PDOException $e)
    {
        echo $e->getMessage();  
        return false;
    }
}

thanks @Brent, I worked on the code with your suggestions and I am getting a different error now - SQLSTATE[HY093]: Invalid parameter number: parameter was not defined — Harold Chintembo, Jun 01 '18 at 13:35
You already have an id_number... Do you also need a separate id? — David Culbreth, Jun 01 '18 at 13:54
You have a typo in your sql. It says: `=:firsname` but it should be `=:firstname`. Missing 't'. Same is for `=:identiy`, should be `=:identity ` — , Jun 01 '18 at 14:28

A new error Invalid parameter number: number of bound variables does not match number of tokens

1 Answers1