SQLSTATE HY093 Invalid parameter number parameter was not defined

Question

BUILD VALUES  count   count  matches   for  i   0   i  lt   count     i         values                 INSERT INTO DATABASE  q    this - gt  dbc - gt  prepare  INSERT INTO hashes  hash  VALUES     implode        values      ON DUPLICATE KEY UPDATE hash   hash     q - gt  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

User · Answer

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    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   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  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- gt bindParam      i   column names  i    param type    stmt- gt bindValue      i   values  i    param type    i             When binding values to column names or table names you can use    but its not necessary  but make sure to be consistent  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

User · Answer

I had the same error  My issue was using the wrong parameter name when binding   Notice  tokenHash in the query  but  token hash when binding  Fixing one or the other resolves the error in this instance      Prepare DB connection  sql    INSERT INTO rememberedlogins  token hash user id expires at          VALUES   tokenHash  user id  expires at     db   static  getDB     stmt    db- gt prepare  sql       Bind values  stmt- gt bindValue   token hash   hashed token PDO  PARAM STR

User · Answer

This error you are receiving       SQLSTATE HY093   Invalid parameter number  parameter was not defined   is because the number of elements in  values  amp   matches is not the same or  matches contains more than 1 element   If  matches contains more than 1 element  than the insert will fail  because there is only 1 column name referenced in the query hash   If  values  amp   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    lt  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- gt getMessage             matches   array  1     count   count  matches   for  i   0   i  lt   count     i         values                INSERT INTO DATABASE  sql    INSERT INTO hashes  hash  VALUES      implode        values       ON DUPLICATE KEY UPDATE hash  hash     stmt    dbh- gt prepare  sql    data    stmt- gt execute  matches      Error reporting if something went wrong    var dump  dbh- gt errorInfo        gt    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   I hope this helps

User · Answer

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       lt  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- gt setAttribute PDO  ATTR ERRMODE  PDO  ERRMODE EXCEPTION       base- gt query  SET character set results    utf8                        character set client    utf8                        character set connection    utf8                        character set database    utf8                        character set server    utf8         base- gt 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- gt prepare  sql       se pasan por parametros aqui    result- gt bindParam   c art    codigoarticulo      result- gt bindParam   n art    nombrearticulo      result- gt bindParam   sec    seccion      result- gt bindParam   pre    precio      result- gt bindParam   fecha art    fecha      result- gt bindParam   import    importado      result- gt bindParam   p orig    paisdeorigen      result- gt execute      echo  Articulo agregado     catch  Exception  e       echo  Error     echo  e- gt getMessage      finally         gt

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

Examples related to php

Examples related to mysql

Examples related to pdo