Page 1 of 1

dates to mysql

Posted: Thu Oct 30, 2003 5:00 am
by yaron
Hello all,
I have a date field in mysql db which is inserted manualy by the user.
This field is optinal in the insert form.
the problem is if the user fills up the form without that field (i.e. the variable that holds the value is null! ) the value inserted to the db is not null but 0000-00-00.
how can i prevent that and just insert a null value...?
I have tried checking the date variable before the insert and turned it into null if it wasn't a real date but still the value inserted was 0000-00-00

What am i doing wrong?

thx

Posted: Thu Oct 30, 2003 5:11 am
by volka
No default value assigned ot the field?
How do you insert a record?
What happens if you manually assign NULL when inserting a record?

Posted: Thu Oct 30, 2003 5:53 am
by yaron
no defaults what so ever
actually it's a simple update query
UPDATE table SET dat_field='$date'

Posted: Thu Oct 30, 2003 6:01 am
by volka
UPDATE table SET dat_field='$date'
whatever $date is, a stringliteral is sent to mysql. Even if $date is unset '' ships to the database. And that's not NULL.

Code: Select all

if (isset($date) && strlen($date) > 0)
   UPDATE table SET dat_field='$date'  <where-clause>
else
   UPDATE table SET dat_field=NULL  <where-clause>

Posted: Thu Oct 30, 2003 6:12 am
by yaron
yea.... I figured there is no way around it...
Thanks