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

You need to use bindValue, not bindParam

bindParam takes a variable 6 by reference, and doesn't pull in a value 5 at the time of calling bindParam. I found this in 4 a comment on the PHP docs:

bindValue(':param', null, PDO::PARAM_INT);

P.S. You may be 3 tempted to do this bindValue(':param', null, PDO::PARAM_NULL); but it did not work 2 for everybody (thank you Will Shaver for 1 reporting.)

When using bindParam() you must pass in a variable, not 3 a constant. So before that line you need 2 to create a variable and set it to null

$myNull = null;
$stmt->bindParam(':v1', $myNull, PDO::PARAM_NULL);

You would 1 get the same error message if you tried:

$stmt->bindParam(':v1', 5, PDO::PARAM_NULL);

When using INTEGER columns (that can be NULL) in MySQL, PDO 5 has some (to me) unexpected behaviour.

If 4 you use $stmt->execute(Array), you have to specify the literal 3 NULL and cannot give NULL by variable reference. So 2 this won't work:

// $val is sometimes null, but sometimes an integer
$stmt->execute(array(
    ':param' => $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->execute(array(
    ':param' => isset($val) ? $val : null
));
// no errors, inserts NULL when $val == null, inserts the integer otherwise

Tried 1 this on MySQL 5.5.15 with PHP 5.4.1

For those who still have problems (Cannot 3 pass parameter 2 by reference), define a 2 variable with null value, not just pass 1 null to PDO:

bindValue(':param', $n = null, PDO::PARAM_INT);

Hope this helps.

I had the same problem and I found this 1 solution working with bindParam :

    bindParam(':param', $myvar = NULL, PDO::PARAM_INT);

If you want to insert NULL only when the value is 9 empty or '', but insert the value when it is available.

A) Receives 8 the form data using POST method, and calls 7 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 6 if a field was not filled up by the user, and 5 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->prepare($sql); 

    //Works with INT, FLOAT, ETC.
    $query->bindValue(':productId',  !empty($productId)   ? $productId   : NULL, PDO::PARAM_INT); 

    //Works with strings.
    $query->bindValue(':productName',!empty($productName) ? $productName : NULL, PDO::PARAM_STR);   

    $query->execute();      
}

For instance, if 4 the user doesn't input anything on the productName field 3 of the form, then $productName will be SET but EMPTY. So, you 2 need check if it is empty(), and if it is, then 1 insert NULL.

Tested on PHP 5.5.17

Good luck,

Several answers have given examples of what 26 you should do. But they haven't really 25 explained why you should do one of those 24 things.

The bindParam method is meant to be used with 23 something like a loop (or just repeated 22 statements). It binds a variable reference. So 21 something like

$stmt = $dbh->prepare('INSERT INTO t1 (v1) VALUES(:v1)');
$stmt->bindParam(':v1', $i, PDO::PARAM_INT);
for ($i = 0; $i < 10; $i++) {
    $stmt->execute();
}

Would insert values 0 through 20 9 in a table.

That's obviously a very simple 19 example that could be implemented in other, more 18 efficient ways. You could have more complex 17 logic here. But the basic idea is that 16 you bind a reference to a variable and then 15 you can change the value of the variable.

You 14 can get around the need for a reference 13 by creating a variable before calling bindParam. But in your case, you don't particularly 12 want to bind to a variable reference. You 11 just want to bind a value. So go ahead 10 and do exactly that with bindValue.

You can mostly 9 just use bindValue. But to show why both methods 8 exist, let's rewrite the previous example 7 to use bindValue instead of bindParam:

$stmt = $dbh->prepare('INSERT INTO t1 (v1) VALUES(:v1)');
for ($i = 0; $i < 10; $i++) {
    $stmt->bindValue(':v1', $i, PDO::PARAM_INT);
    $stmt->execute();
}

This will work, but 6 you have to call bindValue on every iteration of 5 the loop whereas you only needed to call 4 bindParam once. But you aren't doing anything like 3 that, so you can just

$stmt->bindValue(':v1', null, PDO::PARAM_INT);

And everything will 2 work, as stated in the accepted answer. Because you want 1 to bind a value, not a variable reference.