Run PDO with an array containing null values

Question

I need to update the database, and I use the PDO execute() method, providing it with an array as parameters.

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

Here is an example of the request / parameters:

Generated Request:

 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 for id_extra2

In the code for id_extra2 , I have such a condition (the idea is that I have either an identifier or 0, and then I have to update the DB value using NULL ):

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

I tried setting $_POST['id_extra2'] to '' and NULL and 'null' , but it still doesn't work ...

Any idea appreciated! Thanks!

Answer 1

Please consider using bindValue instead of passing an array. As the saying goes here :

All values ​​are processed as PDO :: PARAM_STR.

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

 <?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 has given you headaches in the past, pay attention to the subtle difference between bindValue and bindParam . Personally, I never use bindParam because of side effects that make scripts difficult to understand, although, of course, there are times when these effects will be useful.

EDIT: you could, of course, simplify the function even more and get rid of the need to specify the type as an additional key in the passed array, 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 value passed in the array; however, which is more error prone since, for example, floats are also numeric, and this will lead to an incorrect solution in the above switch statement, but I thought I mentioned this for the sake of completeness.