0

I am trying to enter a NULL value into a MySQL DB when the user does not set a date on a form input. For some reason even when I set the value to NULL before entering it, the value goes to '0000-00-00' in my DB. What I am trying to do is update a date field which had been previously set to a date and now I want to reset it to NULL.

I tried then entering a NULL value directly into my query string but I get an error because of the missing "'". If I did include the "'" around the NULL value then it sets the field to the '0000-00-00'. Unfortunately I cannot simply enter the values via prepared statements because I am creating an entire SQL statement as the prepared statement.

HTML:

<input name="datefield" type="date"/>

PHP:

while($row = $getfields->fetch(PDO::FETCH_ASSOC)) {
    $temp_col = $row['FieldName'];
    $field_value = $_POST[$temp_col];
    if (!isset($field_value) || empty($field_value)) {
        $field_value = NULL;
        $update_array = $temp_col . "=" . $field_value;   <-- PROBLEM HERE!
    } else {
       $update_array = $temp_col . "='" . $field_value . "'";
    }
}
$updatefields=$db->prepare("UPDATE MyMainTable SET $update_array WHERE ItemNum = ?");
$updatefields->execute(array($personid));
Danconia
  • 543
  • 2
  • 12
  • 28
  • Is there a default set for the field in the DB? Make sure it's not set to "not null" and that the default value is "null" – Zarathuztra Feb 26 '14 at 02:27
  • does that field in your DB allow NULL values? – flauntster Feb 26 '14 at 02:27
  • @Zarathuztra, no, there is no default and yes it allows nulls. In fact, the item is NULL unless the user changes it. However, this is an update. Let's say the user had set the date, saves the form, then they go back and delete the date. Now I cannot reset the date to NULL. – Danconia Feb 26 '14 at 02:28
  • Check http://stackoverflow.com/questions/1137655/how-to-insert-an-empty-value-in-mysql-date-type-field – ahPo Feb 26 '14 at 02:28
  • The default for the field is NULL. However, in this situation I am updating the field. This field is part of a user profile. Imagine the user originally sets their date of birth and saves it. Then they go back to their profile and they want to delete this date. In this case I have not been able to reset the value of this field to NULL. So the problem is not originally setting it to NULL if the user does not input it, that works, it is updating it to NULL. @user2340218 – Danconia Feb 26 '14 at 02:32

0 Answers0