Thread: empty form fields, NULLS, quoting, postgreSQL inserts into date, interval fields...

empty form fields, NULLS, quoting, postgreSQL inserts into date, interval fields...

From

Dan Jewett

Date:

28 March 2003, 15:12:37

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.

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

From

Vince Vielhaber

Date:

28 March 2003, 15:21:18

On Fri, 28 Mar 2003, Dan Jewett wrote:

> 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.

embed the single quotes into $trk_length.

if(empty($trk_length))
    $tkln = "NULL";
else
    $tkln = "'$trk_length'";

... VALUES ('$var1', '$var2', $tkln)");

Vince.
--
 Fast, inexpensive internet service 56k and beyond!  http://www.pop4.net/
   http://www.meanstreamradio.com       http://www.unknown-artists.com
         Internet radio: It's not file sharing, it's just radio.

Re: empty form fields, NULLS, quoting, postgreSQL

From

Rod Kreisler

Date:

28 March 2003, 15:21:58

At 12:19 PM 3/28/2003 -0500, Dan Jewett wrote:

>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.

In addition to setting NULL you need to add the quotes to the string if
it's valid.  That way you don't add them in the query itself to avoid
quoting NULL.  Also, you shouldn't be using empty() for this.  Try this:

$trk_length = strlen($_POST['trk_length'])==0 ? NULL :
"'".$_POST['trk_length']."'";
$result=pg_query($conn, "INSERT INTO track (field1, field2, trk_length)
VALUES ('$var1', '$var2', $trk_length)");

HTH

Rod

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

From

Peter Clarke

Date:

28 March 2003, 15:22:58

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
>
>

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

From

Dan Jewett

Date:

28 March 2003, 18:55:51

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.