Skip to content
Advertisement

Using PHP/PDO to set a NULL as value when UPDATE by array

i am executing SQL statement like this with PDO.

$SQL_stmt = "UPDATE `home_slides` SET main_title=?,sub_title=?,slide_type=?,photo=?,video_small=?,video_large=? WHERE `id`=1"
$DBH_stmt = $PDOcon->prepare($SQL_stmt);
$DBH_stmt->execute($_POSTData_array);

and $_POSTData_array is like this

Array ( 
    [0] => PIC_Testings 
    [1] => City 
    [2] => pic 
    [3] => 88c85c6670ac164f0a97c85ce5d25211.jpg 
    [4] => null 
    [5] => null
)

the problem is i can’t get the null as null in db, its update as blank. any help???

UPDATE !

as per comment by Álvaro González i tried var_dump().

var_dump() is much better. I need to insist: how do you know that you’re getting NULL values upon insertion? – Álvaro González

// result of var_dump($_POSTData_array);
array(6) {
  [0] => string(12) "PIC Testings"
  [1] => string(5) "Citys"
  [2] => string(3) "pic"
  [3] => string(36) "88c85c6670ac164f0a97c85ce5d25211.jpg"
  [4] => string(0) ''
  [5] => string(0) ''
}

so actually there was no NULL set in the $_POSTData_array. guess var_dump() way much more batter then print_r() to verifying data.

Advertisement

Answer

Found a Solution.

Solution 1 while creating the array elements use array_push.

$_POSTData_array= []
if (empty($_POST[$FieldName])) {
   // $_POSTData_array[] .= `null`;   // previous method
   array_push($_POSTedData, null);    // new method
} else {
   array_push($_POSTedData, $_POST[$FieldName]);
}

Solution 2 using array_map to set Null to blank elements after creating the array before executing PDO statement.

$_POSTedData_array = array_map(function($value) {
                        return $value === "" ? NULL : $value;
                     }, $_POSTedData_array);

new result of var_dump($_POSTData_array); for both solutions

array(6) {
  [0] => string(12) "PIC Testings"
  [1] => string(5) "Citys"
  [2] => string(3) "pic"
  [3] => string(36) "88c85c6670ac164f0a97c85ce5d25211.jpg"
  [4] => NULL
  [5] => NULL
}

Thanks for all the help everyone.

and to those who ‘downvote‘ my question, guess what! there is a solution after all.

User contributions licensed under: CC BY-SA
8 People found this is helpful
Advertisement