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