pg.ddx.io  pgsql-php@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Peter Clarke <peter@jingo.com>
To: pgsql-php@postgresql.org
Subject: Re: empty form fields, NULLS, quoting, postgreSQL inserts into
Date: Fri, 28 Mar 2003 17:22:27 +0000
Message-ID: <3E8484D3.30008@jingo.com> (raw)
In-Reply-To: <20030328121907666343.GyazMail.danjewett@mac.com>
References: <20030328121907666343.GyazMail.danjewett@mac.com>

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




view thread (5+ messages)  latest in thread

Message-ID: <3E8484D3.30008@jingo.com>
Permalink:  ../3E8484D3.30008@jingo.com/
Also on:    postgresql.org/message-id/3E8484D3.30008@jingo.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-php@postgresql.org
  Cc: peter@jingo.com
  Subject: Re: empty form fields, NULLS, quoting, postgreSQL inserts into
  In-Reply-To: <3E8484D3.30008@jingo.com>
  References: <20030328121907666343.GyazMail.danjewett@mac.com> <3E8484D3.30008@jingo.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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