How do I insert NULL values using PDO?

Question

I'm using this code and I'm beyond frustration:

    try {
         $dbh = new PDO('mysql:dbname=' . DB . ';host=' . HOST, USER, PASS);
         $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
         $dbh->setAttribute(PDO::MYSQL_ATTR_INIT_COMMAND, "SET NAMES 'utf8'");
         }
         catch(PDOException $e)
         {
         ...
         }
$stmt = $dbh->prepare('INSERT INTO table(v1, v2, ...) VALUES(:v1, :v2, ...)');

$stmt->bindParam(':v1', PDO::PARAM_NULL); // --> 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/..."

Thanks.

Answer 1

Try passing the bind paramaters as an array when you execute the query.

$stmt->execute(array( ':v1' => null, ':v2' => ... ))

Answer 2

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->bindParam(':v1', $myNull, PDO::PARAM_NULL);

You would get the same error message if you tried:

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

Answer 3

I'm just learning PDO, but I think you need to use bindValue, not bindParam

bindParam takes a variable, to 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);

Though I think it would make a lot more sense to do this:

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