Why I am getting Cannot pass parameter 2 by reference error when I am using bindParam with a constant value

Question

I m using this code and I m beyond frustration   try        dbh   new PDO  mysql dbname     DB     host     HOST  USER  PASS        dbh- gt setAttribute PDO  ATTR ERRMODE  PDO  ERRMODE EXCEPTION        dbh- gt setAttribute PDO  MYSQL ATTR INIT COMMAND   SET NAMES  utf8       catch PDOException  e               stmt    dbh- gt prepare  INSERT INTO table v1  v2       VALUES  v1   v2           stmt- gt bindParam   v1   PDO  PARAM NULL      -- gt  Here s the problem   PDO  PARAM NULL  null      all of them fail and throw this error      Fatal error  Cannot pass parameter 2 by reference in  opt

User · Accepted Answer

You need to use bindValue  not bindParam bindParam takes a variable by reference  and doesn t pull in a value at the time of calling bindParam  I found this in a comment on the PHP docs  bindValue   param   null  PDO  PARAM INT    P S  You may be tempted to do this bindValue   param   null  PDO  PARAM NULL   but it did not work for everybody  thank you Will Shaver for reporting

User · Answer

When using bindParam   you must pass in a variable  not a constant  So before that line you need to create a variable and set it to null   myNull   null   stmt- gt bindParam   v1    myNull  PDO  PARAM NULL     You would get the same error message if you tried    stmt- gt bindParam   v1   5  PDO  PARAM NULL

User · Answer

I had the same problem and I found this solution working with bindParam        bindParam   param    myvar   NULL  PDO  PARAM INT

User · Answer

When using INTEGER columns  that can be NULL  in MySQL  PDO has some  to me  unexpected behaviour   If you use  stmt- gt execute Array   you have to specify the literal NULL and cannot give NULL by variable reference  So this won t work       val is sometimes null  but sometimes an integer  stmt- gt execute array        param    gt   val        will cause the error  incorrect integer value  when  val    null   But this will work       val again is sometimes null  but sometimes an integer  stmt- gt execute array        param    gt  isset  val     val   null        no errors  inserts NULL when  val    null  inserts the integer otherwise   Tried this on MySQL 5 5 15 with PHP 5 4 1

User · Answer

For those who still have problems  Cannot pass parameter 2 by reference   define a variable with null value  not just pass null to PDO   bindValue   param    n   null  PDO  PARAM INT     Hope this helps

User · Answer

Try This    stmt- gt bindValue   v1   null  PDO  PARAM NULL      -- gt  insert null

User · Answer

If you want to insert NULL only when the value is empty or     but insert the value when it is available   A  Receives the form data using POST method  and calls function insert with those values   insert    POST  productId       Will be set to NULL if empty               POST  productName         Will be to NULL if empty                                   B  Evaluates if a field was not filled up by the user  and inserts NULL if that s the case   public function insert   productId   productName           sql    INSERT INTO products    productId  productName                    VALUES    productId   productName            IMPORTANT  Repace  db with your PDO instance      query    db- gt prepare  sql           Works with INT  FLOAT  ETC       query- gt bindValue   productId     empty  productId       productId     NULL  PDO  PARAM INT           Works with strings       query- gt bindValue   productName   empty  productName     productName   NULL  PDO  PARAM STR            query- gt execute              For instance  if the user doesn t input anything on the productName field of the form  then  productName will be SET but EMPTY  So  you need check if it is empty    and if it is  then insert NULL   Tested on PHP 5 5 17  Good luck

User · Answer

In my case I am using    SQLite  prepared statements with placeholders to handle unknown number of fields  AJAX request sent by user where everything is a string and there is no such thing like NULL value and I desperately need to insert NULLs as that does not violates foreign key constrains  acceptable value     Suppose  now user sends with post    POST field1  with value value1 which can be the empty string    or  null  or  NULL    First I make the statement    stmt    this- gt dbh- gt prepare  INSERT INTO  table    sColumns   VALUES    sValues        where   sColumns  is sth like field1  field2      and   sValues  are my placeholders             Then  I collect my   POST data related with the column names in an array  values and replace with NULLs     for  i   0   i  lt   count  values    i         if   strtolower  values  i       null        values  i                   values  i    null    Now  I can execute    stmt- gt execute  values     and among other bypass foreign key constrains   If on the other hand  an empty string does makes more sense then you have to check if that field is part of a foreign key or not  more complicated

User · Answer

Based on the other answers but with a little more clarity on how to actually use this solution   If for example you have an empty string for a time value but you want to save it as a null     if  endtime              db- gt bind   endtime   endtime NULL PDO  PARAM STR      else       db- gt bind  endtime   endtime         Notice that for time values you would use PARAM STR  as times are stored as strings

[php] Why I am getting Cannot pass parameter 2 by reference error when I am using bindParam with a constant value?

Examples related to php

Examples related to mysql

Examples related to pdo

Examples related to null

Examples related to sql-insert