X-Original-To: pgsql-php@postgresql.org Received: from spampd.localdomain (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id C21F2476369 for ; Fri, 28 Mar 2003 12:22:48 -0500 (EST) Received: from parmenides.zen.co.uk (parmenides.zen.co.uk [212.23.8.69]) by postgresql.org (Postfix) with SMTP id B279947636E for ; Fri, 28 Mar 2003 12:22:38 -0500 (EST) Received: (qmail 4912 invoked from network); 28 Mar 2003 17:22:42 -0000 Received: from protagoras.zen.co.uk (212.23.8.61) by parmenides.zen.co.uk with QMQP; 28 Mar 2003 17:22:42 -0000 Received: from dsl-217-155-185-174.zen.co.uk (HELO jingo.com) (217.155.185.174) by protagoras.zen.co.uk with SMTP; 28 Mar 2003 17:22:41 -0000 X-Zen-Trace: 217.155.185.174 Message-ID: <3E8484D3.30008@jingo.com> Date: Fri, 28 Mar 2003 17:22:27 +0000 From: Peter Clarke User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.3) Gecko/20030312 X-Accept-Language: en-us, en MIME-Version: 1.0 To: pgsql-php@postgresql.org Subject: Re: empty form fields, NULLS, quoting, postgreSQL inserts into References: <20030328121907666343.GyazMail.danjewett@mac.com> In-Reply-To: <20030328121907666343.GyazMail.danjewett@mac.com> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Spam-Status: No, hits=-38.0 required=5.0 tests=BAYES_10,EMAIL_ATTRIBUTION,IN_REP_TO,QUOTED_EMAIL_TEXT, REFERENCES,REPLY_WITH_QUOTES,USER_AGENT_MOZILLA_UA autolearn=ham version=2.50 X-Spam-Level: X-Spam-Checker-Version: SpamAssassin 2.50 (1.173-2003-02-20-exp) X-Archive-Number: 200303/54 X-Sequence-Number: 1966 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 > >