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!

Answer 1

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.