0

I have a form with a start and finish date, so start date can be required, but the finish date is allowed to be blank until the project finishes.

In phymyadmin I have the field finish_date, set to NULL =YES and default=NULL. According to everything I've read, I should now be able to UPDATE the table with finish_date being set to null. enter image description here

Here is the code to set the POST values. If not valid date, its set to NULL.

if (validDate($_POST['finish_date'], $format = 'Y-m-d')){
    $finish_date = date('Y-m-d', strtotime(trim($_POST['finish_date'])));
  } else {
    $finish_date = NULL;
  }

Here is the error I get when submitting, as you can see, instead of NULL finish_date=""

UPDATE projects
SET title='Love show  me how', project_status='Melody Idea', bpm='144', genre='New Age', release_genre='99', 
vocals='1', lyrics_written='0', voice_of='', start_date='2018-11-11', finish_date='', 
project_time='1hr 30 min', file_name='love show me how', mixed='0', mastered='0', mixed_by='Norman Love', 
mastered_by='', loudness_level='', release_date='1969-12-31', artwork_link='', 
audio_link='', length='', lyrics_link='', priority='7', notes='try and get to this one, has something', lyrics='Mostly completed
uplifting, inspirational', 
project_key='',featuring='', producers='' WHERE id='17 '
LIMIT 1
SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect date value: '' for column 'finish_date' at row 1

I can't seem to be able to figure this out. There are many postings here with "similar" problem, but I have tried all their solutions and none of them seem to work. It appears what Im doing is correct and should work.

UPDATE: NO my question is not the same as the null VS "" question. My issue was caused from NOT using prepared statements. Apparently PDO handles allowing NULL values into MYSQL differently, depending on if you use prepareds' or not. Using them solved my issue.

Norman Bird
  • 642
  • 3
  • 10
  • 29
  • 2
    If you used prepared statements you wouldn't have this problem. Instead, you will need to modify your query from something like `finish_date='$finish_date'` to `finish_date=" . (is_null($finish_date) ? "NULL" . "'$finish_date'") . "` – Nick May 13 '19 at 00:41
  • @Nick, I pondered that but didn't see anyone else mention it. Im going to change to prepared statements, which I should be using anyway, and see if it goes away. Ill report back soon. – Norman Bird May 13 '19 at 00:46
  • 1
    @Nick you were correct. Using prepared allowed the NULL values in without fatal error. Kinda weird but my issue is now solved. Can you post it so I can credit you please? – Norman Bird May 13 '19 at 01:25
  • @Nick, while using prepared statements, the MYSQL is not allowing fields with strings with apostrophe's like O'riley. I thought prepared statements handled this. What do you suggest? – Norman Bird May 13 '19 at 02:24
  • I'm glad that solved your problem with NULLs. I don't understand why you would have problems with strings with apostrophes, as you say, that is another of the issues that prepared statements should handle. I suggest posting a new question, including your code for preparing and executing the statement and some data values you are having problems with. – Nick May 13 '19 at 03:23

0 Answers0