Re: empty form fields, NULLS, quoting, postgreSQL inserts - Mailing list pgsql-php

From Dan Jewett
Subject Re: empty form fields, NULLS, quoting, postgreSQL inserts
Date
Msg-id 20030328160218810017.GyazMail.danjewett@mac.com
Whole thread Raw
In response to Re: empty form fields, NULLS, quoting, postgreSQL inserts into  (Peter Clarke <peter@jingo.com>)
List pgsql-php
On Fri, 28 Mar 2003 17:22:27 +0000, Peter Clarke wrote this well
considered message:
> 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@postgresql.org
>>
>>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster

Vince, Rod, and Peter thanks for your very helpful answers!

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


pgsql-php by date:

Previous
From: Peter Clarke
Date:
Subject: Re: empty form fields, NULLS, quoting, postgreSQL inserts into
Next
From: "Devi Munandar"
Date:
Subject: function not valid