Skip site navigation (1) Skip section navigation (2)

Re: empty form fields, NULLS, quoting, postgreSQL inserts into

From: Peter Clarke <peter(at)jingo(dot)com>
To: pgsql-php(at)postgresql(dot)org
Subject: Re: empty form fields, NULLS, quoting, postgreSQL inserts into
Date: 2003-03-28 17:22:27
Message-ID: 3E8484D3.30008@jingo.com (view raw or flat)
Thread:
Lists: pgsql-php
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(at)postgresql(dot)org
> 
> 


In response to

Responses

pgsql-php by date

Next:From: Dan JewettDate: 2003-03-28 21:02:18
Subject: Re: empty form fields, NULLS, quoting, postgreSQL inserts
Previous:From: Rod KreislerDate: 2003-03-28 17:21:35
Subject: Re: empty form fields, NULLS, quoting, postgreSQL

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group