X-Original-To: pgsql-php@postgresql.org Received: from spampd.localdomain (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 2531A476389 for ; Fri, 28 Mar 2003 12:21:47 -0500 (EST) Received: from server.yourfacility.com (server.yourfacility.com [207.103.74.10]) by postgresql.org (Postfix) with SMTP id 375EB476372 for ; Fri, 28 Mar 2003 12:21:40 -0500 (EST) Received: (qmail 6837 invoked by uid 0); 28 Mar 2003 17:21:44 -0000 Received: from pcp01408378pcs.norstn01.pa.comcast.net (HELO rodk.23net.net) (68.80.144.23) by server.yourfacility.com with SMTP; 28 Mar 2003 17:21:44 -0000 Message-Id: <5.2.0.9.0.20030328121631.01b16e30@127.0.0.1> X-Sender: 207.103.74.10:rod@23net.net@127.0.0.1 X-Mailer: QUALCOMM Windows Eudora Version 5.2.0.9 Date: Fri, 28 Mar 2003 12:21:35 -0500 To: Dan Jewett , pgsql-php@postgresql.org From: Rod Kreisler Subject: Re: empty form fields, NULLS, quoting, postgreSQL In-Reply-To: <20030328121907666343.GyazMail.danjewett@mac.com> Mime-Version: 1.0 Content-Type: text/plain; charset="us-ascii"; format=flowed X-Spam-Status: No, hits=-12.8 required=5.0 tests=BAYES_20,EMAIL_ATTRIBUTION,IN_REP_TO 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/53 X-Sequence-Number: 1965 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