SQLSTATE[HY093]: Invalid parameter number: parameter was not defined

Question

// BUILD VALUES $count = count($matches); for($i = 0; $i < $count; ++$i) {     $values[] = '(?)'; } // INSERT INTO DATABASE $q = $this->dbc->prepare("INSERT INTO hashes (hash) VALUES " . implode(', ', $values) . " ON DUPLICATE KEY UPDATE hash = hash"); $q->execute($matches); 

The code above fails with the following error

SQLSTATE[HY093]: Invalid parameter number: parameter was not defined

Although when count($matches) == count($values) just before execute is called?

What is going on here?

Answer 1

This error you are receiving:

SQLSTATE[HY093]: Invalid parameter number: parameter was not defined

is because the number of elements in $values & $matches is not the same or $matches contains more than 1 element.

If $matches contains more than 1 element, then the insert will fail, because there is only 1 column name referenced in the query(hash)

If $values & $matches do not contain the same number of elements then the insert will also fail, due to the query expecting x params but it is receiving y data $matches .

I believe you will also need to ensure the column hash has a unique index on it as well.

Try the code here:

<?php  /*** mysql hostname ***/ $hostname = 'localhost';  /*** mysql username ***/ $username = 'root';  /*** mysql password ***/ $password = '';  try {     $dbh = new PDO("mysql:host=$hostname;dbname=test", $username, $password);     /*** echo a message saying we have connected ***/     echo 'Connected to database';     } catch(PDOException $e)     {     echo $e->getMessage();     }     $matches = array('1'); $count = count($matches); for($i = 0; $i < $count; ++$i) {     $values[] = '?'; }  // INSERT INTO DATABASE $sql = "INSERT INTO hashes (hash) VALUES (" . implode(', ', $values) . ") ON DUPLICATE KEY UPDATE hash='hash'"; $stmt = $dbh->prepare($sql); $data = $stmt->execute($matches);  //Error reporting if something went wrong... var_dump($dbh->errorInfo());  ?> 

You will need to adapt it a little.

Table structure I used is here:

CREATE TABLE IF NOT EXISTS `hashes` (   `hashid` int(11) NOT NULL AUTO_INCREMENT,   `hash` varchar(250) NOT NULL,   PRIMARY KEY (`hashid`),   UNIQUE KEY `hash1` (`hash`) ) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ; 

Code was run on my XAMPP Server which is using PHP 5.3.8 with MySQL 5.5.16.

Answer 2

SQLSTATE[HY093]: Invalid parameter number: parameter was not defined

Unfortunately this error is not descriptive for a range of different problems related to the same issue - a binding error. It also does not specify where the error is, and so your problem is not necessarily in the execution, but the sql statement that was already 'prepared'.

These are the possible errors and their solutions:

1. There is a parameter mismatch - the number of fields does not match the parameters that have been bound. Watch out for arrays in arrays. To double check - use var_dump($var). "print_r" doesn't necessarily show you if the index in an array is another array (if the array has one value in it), whereas var_dump will.

2. You have tried to bind using the same binding value, for example: ":hash" and ":hash". Every index has to be unique, even if logically it makes sense to use the same for two different parts, even if it's the same value. (it's similar to a constant but more like a placeholder)

3. If you're binding more than one value in a statement (as is often the case with an "INSERT"), you need to bindParam and then bindValue to the parameters. The process here is to bind the parameters to the fields, and then bind the values to the parameters.

   // Code snippet $column_names = array(); $stmt->bindParam(':'.$i, $column_names[$i], $param_type); $stmt->bindValue(':'.$i, $values[$i], $param_type); $i++; //..... 

4. When binding values to column_names or table_names you can use `` but its not necessary, but make sure to be consistent.

5. Any value in '' single quotes is always treated as a string and will not be read as a column/table name or placeholder to bind to.