Error when preparing a multiple insert query

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 is because the number of elements in $values & $matches does not match.

If $values & $matches do not contain the same number of elements then the insert will fail, due to the query expecting X params but it is receiving Y data $matches. In your case, $values probably already contains some values, which is the reason for the count mismatch. To avoid that, you must always initialize an array before the loop.

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

$matches = array('1');
$count = count($matches);
$values = [];
for($i = 0; $i < $count; ++$i) {
    $values[] = '(?)';
}

// INSERT INTO DATABASE
$sql = "INSERT INTO hashes (hash) VALUES " . implode(', ', $values) . " ON DUPLICATE KEY UPDATE hash=values(hash)";
$stmt = $dbh->prepare($sql);
$data = $stmt->execute($matches);

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 you're inconsistent in using The backtick to delimit literals that represent identifiers (not strings). (Using ``), but once you use them you have to be consistent for that query, i.e. you can't use backticks for one identifier and not use them for another, all have to have backticks if you use them. (e.g. SELECT id FROM my_table) Do not use back-ticks for placeholders

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

Answer 3

I had the same error after using the wrong parameter name when binding.

Notice :tokenHash in the VALUES clause of the query, but :token_hash when binding.

Fixing one or the other resolved the error.

// Prepare DB connection
$sql = 'INSERT INTO rememberedlogins (token_hash,user_id,expires_at)
        VALUES (:tokenHash,:user_id,:expires_at)';
$db = static::getDB();
$stmt = $db->prepare($sql);

// Bind values
$stmt->bindValue(':token_hash',$hashed_token,PDO::PARAM_STR);

Answer 4

The same error I found will show if you have a mismatch of the column name in PHP & the database column name, Double check that too. This is what I had wrong.

Answer 5

I understand that the answer was useful however for some reason it does not work for me however I have moved the situation with the following code and it is perfect

    <?php

$codigoarticulo = $_POST['codigoarticulo'];
$nombrearticulo = $_POST['nombrearticulo'];
$seccion        = $_POST['seccion'];
$precio         = $_POST['precio'];
$fecha          = $_POST['fecha'];
$importado      = $_POST['importado'];
$paisdeorigen   = $_POST['paisdeorigen'];
try {

  $server = 'mysql: host=localhost; dbname=usuarios';
  $user   = 'root';
  $pass   = '';
  $base   = new PDO($server, $user, $pass);

  $base->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

  $base->query("SET character_set_results = 'utf8',
                     character_set_client = 'utf8',
                     character_set_connection = 'utf8',
                     character_set_database = 'utf8',
                     character_set_server = 'utf8'");

  $base->exec("SET character_set_results = 'utf8',
                     character_set_client = 'utf8',
                     character_set_connection = 'utf8',
                     character_set_database = 'utf8',
                     character_set_server = 'utf8'");

  $sql = "
  INSERT INTO productos
  (CÓDIGOARTÍCULO, NOMBREARTÍCULO, SECCIÓN, PRECIO, FECHA, IMPORTADO, PAÍSDEORIGEN)
  VALUES
  (:c_art, :n_art, :sec, :pre, :fecha_art, :import, :p_orig)";
// SE ejecuta la consulta ben prepare
  $result = $base->prepare($sql);
//  se pasan por parametros aqui
  $result->bindParam(':c_art', $codigoarticulo);
  $result->bindParam(':n_art', $nombrearticulo);
  $result->bindParam(':sec', $seccion);
  $result->bindParam(':pre', $precio);
  $result->bindParam(':fecha_art', $fecha);
  $result->bindParam(':import', $importado);
  $result->bindParam(':p_orig', $paisdeorigen);
  $result->execute();
  echo 'Articulo agregado';
} catch (Exception $e) {

  echo 'Error';
  echo $e->getMessage();
} finally {

}

?>