X-Original-To: pgsql-php@postgresql.org Received: from spampd.localdomain (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 1A5A1475E91 for ; Fri, 28 Mar 2003 12:12:37 -0500 (EST) Received: from smtpout.mac.com (smtpout.mac.com [17.250.248.86]) by postgresql.org (Postfix) with ESMTP id 73F4D475E77 for ; Fri, 28 Mar 2003 12:12:36 -0500 (EST) Received: from asmtp01.mac.com (asmtp01-qfe3 [10.13.10.65]) by smtpout.mac.com (Xserve/MantshX 2.0) with ESMTP id h2SHCadg021260 for ; Fri, 28 Mar 2003 09:12:41 -0800 (PST) Received: from 169.254.209.243 ([162.84.128.118]) by asmtp01.mac.com (Netscape Messaging Server 4.15) with ESMTP id HCGYGZ00.6G6 for ; Fri, 28 Mar 2003 09:12:35 -0800 Date: Fri, 28 Mar 2003 12:19:07 -0500 From: Dan Jewett To: pgsql-php@postgresql.org Message-ID: <20030328121907666343.GyazMail.danjewett@mac.com> Subject: empty form fields, NULLS, quoting, postgreSQL inserts into date, interval fields... 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=-2.6 required=5.0 tests=BAYES_20,RCVD_IN_OSIRUSOFT_COM version=2.50 X-Spam-Level: X-Spam-Checker-Version: SpamAssassin 2.50 (1.173-2003-02-20-exp) X-Archive-Number: 200303/51 X-Sequence-Number: 1963 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.