Re: empty form fields, NULLS, quoting, postgreSQL inserts into

Dan Jewett wrote:
> 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')");
>

Only use quotes if you have a value:

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

$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.
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org
>
>