dates to mysql

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
yaron
Forum Contributor
Posts: 157
Joined: Fri Aug 22, 2003 8:40 am

dates to mysql

Post 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
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post 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?
yaron
Forum Contributor
Posts: 157
Joined: Fri Aug 22, 2003 8:40 am

Post by yaron »

no defaults what so ever
actually it's a simple update query
UPDATE table SET dat_field='$date'
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post 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>
yaron
Forum Contributor
Posts: 157
Joined: Fri Aug 22, 2003 8:40 am

Post by yaron »

yea.... I figured there is no way around it...
Thanks
Post Reply