pg.ddx.io  pgsql-php@postgresql.org mailing list archive  
help / color / mirror / Atom feed
empty form fields, NULLS, quoting, postgreSQL inserts into date, interval fields...
5+ messages / 4 participants
[nested] [flat]

* empty form fields, NULLS, quoting, postgreSQL inserts into date, interval fields...
@ 2003-03-28 17:19 Dan Jewett <danjewett@mac.com>
  2003-03-28 17:21 ` Re: empty form fields, NULLS, quoting, postgreSQL inserts into Vince Vielhaber <vev@michvhf.com>
  2003-03-28 17:21 ` Re: empty form fields, NULLS, quoting, postgreSQL Rod Kreisler <rod@23net.net>
  2003-03-28 17:22 ` Re: empty form fields, NULLS, quoting, postgreSQL inserts into Peter Clarke <peter@jingo.com>
  0 siblings, 3 replies; 5+ messages in thread

From: Dan Jewett @ 2003-03-28 17:19 UTC (permalink / raw)
  To: 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.




^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: empty form fields, NULLS, quoting, postgreSQL inserts into
  2003-03-28 17:19 empty form fields, NULLS, quoting, postgreSQL inserts into date, interval fields... Dan Jewett <danjewett@mac.com>
@ 2003-03-28 17:21 ` Vince Vielhaber <vev@michvhf.com>
  2 siblings, 0 replies; 5+ messages in thread

From: Vince Vielhaber @ 2003-03-28 17:21 UTC (permalink / raw)
  To: Dan Jewett <danjewett@mac.com>; +Cc: pgsql-php

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.




^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: empty form fields, NULLS, quoting, postgreSQL
  2003-03-28 17:19 empty form fields, NULLS, quoting, postgreSQL inserts into date, interval fields... Dan Jewett <danjewett@mac.com>
@ 2003-03-28 17:21 ` Rod Kreisler <rod@23net.net>
  2 siblings, 0 replies; 5+ messages in thread

From: Rod Kreisler @ 2003-03-28 17:21 UTC (permalink / raw)
  To: Dan Jewett <danjewett@mac.com>; pgsql-php

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




^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: empty form fields, NULLS, quoting, postgreSQL inserts into
  2003-03-28 17:19 empty form fields, NULLS, quoting, postgreSQL inserts into date, interval fields... Dan Jewett <danjewett@mac.com>
@ 2003-03-28 17:22 ` Peter Clarke <peter@jingo.com>
  2003-03-28 21:02   ` Re: empty form fields, NULLS, quoting, postgreSQL inserts Dan Jewett <danjewett@mac.com>
  2 siblings, 1 reply; 5+ messages in thread

From: Peter Clarke @ 2003-03-28 17:22 UTC (permalink / raw)
  To: pgsql-php

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




^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: empty form fields, NULLS, quoting, postgreSQL inserts
  2003-03-28 17:19 empty form fields, NULLS, quoting, postgreSQL inserts into date, interval fields... Dan Jewett <danjewett@mac.com>
  2003-03-28 17:22 ` Re: empty form fields, NULLS, quoting, postgreSQL inserts into Peter Clarke <peter@jingo.com>
@ 2003-03-28 21:02   ` Dan Jewett <danjewett@mac.com>
  0 siblings, 0 replies; 5+ messages in thread

From: Dan Jewett @ 2003-03-28 21:02 UTC (permalink / raw)
  To: 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.




^ permalink  raw  reply  [nested|flat] 5+ messages in thread


end of thread, other threads:[~2003-03-28 21:02 UTC | newest]

Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-03-28 17:19 empty form fields, NULLS, quoting, postgreSQL inserts into date, interval fields... Dan Jewett <danjewett@mac.com>
2003-03-28 17:21 ` Re: empty form fields, NULLS, quoting, postgreSQL inserts into Vince Vielhaber <vev@michvhf.com>
2003-03-28 17:21 ` Re: empty form fields, NULLS, quoting, postgreSQL Rod Kreisler <rod@23net.net>
2003-03-28 17:22 ` Re: empty form fields, NULLS, quoting, postgreSQL inserts into Peter Clarke <peter@jingo.com>
2003-03-28 21:02   ` Re: empty form fields, NULLS, quoting, postgreSQL inserts Dan Jewett <danjewett@mac.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox