empty form fields, NULLS, quoting, postgreSQL inserts into date, interval fields... - Mailing list pgsql-php

Greetings all,

I realize this revisits an old topic, but I haven't been able to put
together a definitive answer to my problem by checking other sources.

Assuming that that the postgres table fields are configured to allow
NULLs, and I wish to allow certain form fields to remain unfilled....

I'm having trouble getting NULLs into date/time fields and interval
fields.

I've gotten this far:

$trk_length = $_POST['trk_length'];
if (empty($trk_length))                    //or if($trk_length == '')
    $trk_length = NULL;

or

$length_str = $_POST['trk_length'];
    if (empty($length_str))
        $trk_length = NULL;
    else $trk_length = $length_str;

The insert:

$result = pg_query($conn, "INSERT INTO track (field1, field2,
trk_length) VALUES ('$var1', '$var2', '$trk_length')");

This results in a "bad external representation ''." error for the
insert.  If I use double quotes, ie. $trk_length = "NULL"; and remove
the single quotes from the $trk_length variable in the insert
statement, the insert succeeds.  But now, if $trk_length is not empty,
the insert fails with a parse error on the : character in my interval
string.

The same is true for trying to insert NULLS into a date or time field
in postgres.  I believe I have a quoting problem, but I can't figure it
out. Setting the default values of the fields to NULL has not seemed to
help.

Can someone lend some expertise on this?  I've read of others people
struggling with this.  If we had a good answer to this maybe we good
get it into the interactive docs?

Thanks,
Dan


_________________
Visit Eva, Anne, and Dan at: http://www.thenormalfamily.net.