Need help finding cause of PHP Error: Invalid parameter number: parameter was not defined

Question

I have not been able to find the cause of this error. I understand that it usually is caused by the number of params and values not matching during the execute() function. However, I have used var_dump and echo to verify repeatedly that my params and values match in number at every stage of this process. Can someone please show me where my code is wrong? Thanks!

First, here's my initial code:

$insert = array(
    array(  
      'fName' => 'Bob',   
      'mName' => 'C',  
      'lName' => 'Smith',  
      'suffix' => 'Jr'   
    ),  
    array(  
      'fName' => 'Tim',  
      'mName' => 'K',  
      'lName' => 'Jones',  
      'suffix' => 'Sr'  
    ),  
    array(  
      'fName' => 'Jim',  
      'mName' => 'P',  
      'lName' => 'Hampton',  
      'suffix' => 'III'  
    )  
);


$db = new Connect('clients');  
$db->insertMultiple($insert);

Then, here is my relevent class functions:

public function insertMultiple($array)
{   
        foreach($array as $inner)
        {
            $fields = '(';
            $values = '(';

            foreach ($inner as $key => $value)
            {

                $fields .= $key . ',';
                $values .= ':' . $value . ',';
            }

            if (substr($fields, -1, 1) == ',')
            {
                $fields = substr($fields, 0, -1);
            }

            if (substr($values, -1, 1) == ',')
            {
                $values = substr($values, 0, -1);
            }

            $fields .= ')';
            $values .= ')';

            $sql = "INSERT INTO $this->name $fields VALUES $values";

            $this->query($sql);

            $this->bindValues($inner);

            try
            {

                $this->execute();
            }
            catch(PDOException $e)
            {
                $date = new DateTime(); 

                file_put_contents($this->file, trim($this->error = $e->getMessage()). ' ' . $date->format('Y-m-d H:i:s') . PHP_EOL,FILE_APPEND);
            }                   
        }
}

And the functions that this one calls are:

public function bindValues($array)
{   
    foreach($array as $param => $value)
    {
        $param = ':' . $param;

        $this->bind($param,$value); 
    }

}

and

public function bind($param, $value, $type = null)
{
    if (is_null($type)) 
    {
        switch (true) 
        {
            case is_int($value):
                $type = PDO::PARAM_INT;
                break;
            case is_bool($value):
                $type = PDO::PARAM_BOOL;
                break;
            case is_null($value):
                $type = PDO::PARAM_NULL;
                break;
            default:
                $type = PDO::PARAM_STR;
        }
    }
    $this->stmt->bindValue($param, $value, $type);
}

My query method:

public function query($query)
{
    $this->stmt = $this->dbh->prepare($query);
}

Someone please point out where I am going wrong! Thanks!

Have you put `error_reporting` to strict (-1)? This might help in case of misspelled or undefined variables. Also, have you echoed the `$sql` that you generated? Does that look OK? — KrekkieD, May 18 '14 at 12:34
@Gregg, thanks for the input. Yes, my statment looks perfect when I echo it, and as for the error config, I have set up my $options to: PDO::ATTR_PERSISTENT => false and PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION. — DonC, May 18 '14 at 12:38
But no I have not set it to strict(-1). I will look into that going forward, however I have verified that there are no mispellings or undefined varibles currently... — DonC, May 18 '14 at 12:40
The exact error is "SQLSTATE[HY093]: Invalid parameter number:parameter was not defined" and it appears in triplicate for each array I am passing to the function. It occurs in the catch part of the try/catch block for the execute() statment above. — DonC, May 18 '14 at 12:49
Are you prefixing the values twice with a `:`? Once in `insertMultiple` and once in `bindValues`? As that would probably be incorrect — KrekkieD, May 18 '14 at 12:52
No, in the insertMultiple function I am prefixing with the : to build the named placeholders in the sql statement only, and in the bindValues function I am prefixing the : to the params I pass to the bind function. — DonC, May 18 '14 at 13:07

score 0 · Accepted Answer · answered May 18 '14 at 13:21

Looks like you need to change this:

    foreach ($inner as $key => $value)
        {

            $fields .= $key . ',';
            $values .= ':' . $value . ',';
        }

To this:

    foreach ($inner as $key => $value)
        {

            $fields .= $key . ',';
            $values .= ':' . $key . ',';
        }

The value is actually a place holder which is often identical to the key prefixed with :. If I read your code correctly, you are prefixing the actual value. Therefore your bind is not working as it is correctly looking for :key but you had the placeholder incorrectly defined as :value.

It would explain the error as it cannot find the parameters you are looking for.

That was it! Geeze I don't know how I overlooked that. Thanks so much. — DonC, May 18 '14 at 13:32

Need help finding cause of PHP Error: Invalid parameter number: parameter was not defined

1 Answers1