Execute PDO with an array containing null values [duplicate]

Question

I need to update a database and I use PDO's execute() method by giving it an array as parameters.

The idea is that it gives me an error when trying to insert a NULL value...

Here's an example of query/parameters sent:

Generated query:

UPDATE table SET name=?, id_extra1=?, id_extra2=? WHERE id_something=?

Array of parameters:

array (size=8)
  'name' => string 'testing' (length=6)
  'id_extra1' => string '2' (length=1)
  'id_extra2' => null
  'id_something' => string '1958' (length=4)

So the NULL value is for id_extra2

In the code for id_extra2 I have a condition like this (the idea is that I have either an ID or 0 and then I have to update the DB value with NULL):

if ($_POST['id_extra2']==0) {
    $_POST['id_extra2'] = null;
}

I tried setting $_POST['id_extra2'] to '' and to NULL and to 'null' but it's still not working.

Answer 1

Please consider using bindValue instead of passing the array to execute. As it says here:

All values are treated as PDO::PARAM_STR.

It should be possible to make this pretty transparent to the rest of your application, since you already have the values you want to UPDATE as an array. Try e.g. something like this:

<?php
function executeWithDataTypes(PDOStatement $sth, array $values) {
    $count = 1;
    foreach($values as $value) {
        $sth->bindValue($count, $values['value'], $values['type']);
        $count++;
    }

    return $sth->execute();
}

$sth = $handle->prepare("UPDATE table SET name = ?, id_extra1 = ?, id_extra2 = ? WHERE id_something = ?");

$values = array();
$values[] = array('value' => 'testing', 'type' => PDO::PARAM_STR);
$values[] = array('value' => 2, 'type' => PDO::PARAM_INT);
$values[] = array('value' => null, 'type' => PDO::PARAM_NULL);
$values[] = array('value' => 1958, 'type' => PDO::PARAM_INT);

$result = executeWithDataTypes($sth, $values);
?>

As you noted that using bindParam gave you headaches in the past, please be aware of the subtle difference between bindValue and bindParam. Personally, I never use bindParam because of side effects which make it harder to understand scripts, though there are of course cases where these effects will come in handy.

EDIT: You could of course simplify the function even more and get rid of the need of specifying the type as additional key in the passed array by doing something like:

$type = PDO::PARAM_STR;
switch(true) {
    case is_null($value): $type = PDO::PARAM_NULL; break;
    case is_numeric($value): $type = PDO::PARAM_INT; break;
    // ...
    default: break;
}

and determine the type based on the type of the value passed in the array; however, that's more error-prone, since e.g. floats are also numeric and that would lead to a wrong decision in the above switch statement, but I thought I'd mention it for the sake of completeness.