blank date fields cause UPDATE sql string to fail

[xaos]

I am writing a php frontend to our postgresql server and am having a problem with null date fields. When a user logs in they choose a students from a dynamic select list, which opens a page with student info. they can then choose between several forms containing different info on that student. Read-only is fine, no problems. The problem is when I am trying to write the update script so they can update the information provided in the table. Several of the fields in the noted table are date fields. There are about 20 fields in this particular table. I wrote an UPDATE script for all of these fields, to update to the fields with the values posted from the update page. The problem is that if a user wants to update a single field it won't update it if any of the date fields contain no data. These fields are not "NOT NULL" fields in postgresql so i know that is not the problem, and in the previous access frontend there was no problem leaving these fields blank. I just can't figure out why when updating from the PHP script, it won't accept it unless the date fields containa date. I even tried posting "NA" to fill the date fields but it will not take it. It will only take dates. At the moment I just have the input fields defaulting to 1900-1-1, but this is going to confuse users. ANyone know of how i can fix this problem and let them leave the fields blank if they do not have any info fo r them?

[eoghain]

This shouldn't be a problem. How are you building your update statements? Have you tried including just 2 single quotes for the null values, or how about the word null?

UPDATE sometable
SET date1 = '',
    date2 = '',
    date3 = '10/29/02'
WHERE id = 1;

or

UPDATE sometable
SET date1 = null,
    date2 = null,
    date3 = '10/29/02'
WHERE id = 1;

Or what about just not including the null dates in your update statement at all?

[michael]

Try:

$query = 'update mytable set date1=NULL where id ='.$ID;