X-Original-To: pgsql-php@postgresql.org Received: from spampd.localdomain (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 9BB4D476241 for ; Fri, 28 Mar 2003 15:55:50 -0500 (EST) Received: from smtpout.mac.com (smtpout.mac.com [17.250.248.89]) by postgresql.org (Postfix) with ESMTP id A51DF475ED4 for ; Fri, 28 Mar 2003 15:55:45 -0500 (EST) Received: from asmtp01.mac.com (asmtp01-qfe3 [10.13.10.65]) by smtpout.mac.com (Xserve/MantshX 2.0) with ESMTP id h2SKtkDU008580 for ; Fri, 28 Mar 2003 12:55:46 -0800 (PST) Received: from 169.254.209.243 ([162.84.128.118]) by asmtp01.mac.com (Netscape Messaging Server 4.15) with ESMTP id HCH8SY00.0T0 for ; Fri, 28 Mar 2003 12:55:46 -0800 Date: Fri, 28 Mar 2003 16:02:18 -0500 From: Dan Jewett To: pgsql-php@postgresql.org Message-ID: <20030328160218810017.GyazMail.danjewett@mac.com> In-Reply-To: <3E8484D3.30008@jingo.com> References: <20030328121907666343.GyazMail.danjewett@mac.com> <3E8484D3.30008@jingo.com> Subject: Re: empty form fields, NULLS, quoting, postgreSQL inserts Mime-Version: 1.0 (GMessage framework 0.9.9.6) Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Mailer: GyazMail version 0.9.9.6 X-Spam-Status: No, hits=-24.7 required=5.0 tests=BAYES_10,IN_REP_TO,QUOTED_EMAIL_TEXT,RCVD_IN_OSIRUSOFT_COM, REFERENCES,REPLY_WITH_QUOTES 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/55 X-Sequence-Number: 1967 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.