php - Execute PDO with an array containing null values -

i need update database , use pdo's execute() method giving array parameters.

the idea gives me error when trying insert null value...

here's example of query / parameters sent:

generated query :

update table set name=?, id_extra1=?, id_extra2=? 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 null value id_extra2

in code id_extra2 have condition (the idea have either id, either 0 , have update db value null):

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

i tried setting $_post['id_extra2'] '' , null , 'null' it's still not working...

any idea appreciated! thanks!

please consider using bindvalue instead of passing array execute. says here:

all values treated pdo::param_str.

it should possible make pretty transparent rest of application, since have values want update array. try e.g. this:

<?php function executewithdatatypes(pdostatement $sth, array $values) {     $count = 1;     foreach($values $value) {         $sth->bindvalue($count, $values['value'], $values['type']);         $count++;     }      return $sth->execute(); }  $sth = $handle->prepare("update table set name = ?, id_extra1 = ?, id_extra2 = ? 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 noted using bindparam gave headaches in past, please aware of subtle difference between bindvalue , bindparam. personally, never use bindparam because of side effects make harder understand scripts, though there of course cases these effects come in handy.

edit: of course simplify function more , rid of need of specifying type additional key in passed array doing 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 type based on type of value passed in array; however, that's more error-prone, since e.g. floats numeric , lead wrong decision in above switch statement, thought i'd mention sake of completeness.