pg.ddx.io pgsql-php@postgresql.org mailing list archive
help / color / mirror / Atom feedempty 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>
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:21 Vince Vielhaber <vev@michvhf.com>
parent: Dan Jewett <danjewett@mac.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:21 Rod Kreisler <rod@23net.net>
parent: Dan Jewett <danjewett@mac.com>
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:22 Peter Clarke <peter@jingo.com>
parent: 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 21:02 Dan Jewett <danjewett@mac.com>
parent: Peter Clarke <peter@jingo.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