Thread: question
I have a rating section on a website. It has radio
buttons with values 1-5 according to the rating level.
Lastly there is a null option for n/a. We use null for
n/a so it's excluded from the AVG() calculations.
We used nullif('$value','') on inserts in mssql. We
moved to postgres and love it but the nullif() doesn't
match empty strings to each other to return null other
than a text type, causing an error. This is a major
part of our application.
AKA nullif('1','') would insert 1 as integer even
though wrapped in ''. Also nullif('','') would
evaluate NULL and insert the "not a value"
accordingly.
Is there a workaround for this so it doesn't break our
rating system? We cannot always enter a value for a
integer column. Is there any other way to accomplish
this? Please help.
__________________________________
Do you Yahoo!?
Yahoo! Mail - You care about security. So do we.
http://promotions.yahoo.com/new_mail
Matt, In PostgreSQL 8.0.3, I see: postgres=# select nullif( '1', '' ); nullif -------- 1 (1 row) postgres=# select nullif( '', '' ) is null; ?column? ---------- t (1 row) What behavior are you expecting? -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC Strategic Open Source: Open Your i™ http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-469-5150 615-469-5151 (fax) On Aug 24, 2005, at 12:05 AM, Matt A. wrote: > I have a rating section on a website. It has radio > buttons with values 1-5 according to the rating level. > Lastly there is a null option for n/a. We use null for > n/a so it's excluded from the AVG() calculations. > > We used nullif('$value','') on inserts in mssql. We > moved to postgres and love it but the nullif() doesn't > match empty strings to each other to return null other > than a text type, causing an error. This is a major > part of our application. > > AKA nullif('1','') would insert 1 as integer even > though wrapped in ''. Also nullif('','') would > evaluate NULL and insert the "not a value" > accordingly. > > Is there a workaround for this so it doesn't break our > rating system? We cannot always enter a value for a > integer column. Is there any other way to accomplish > this? Please help. > > > > > __________________________________ > Do you Yahoo!? > Yahoo! Mail - You care about security. So do we. > http://promotions.yahoo.com/new_mail > > ---------------------------(end of > broadcast)--------------------------- > TIP 2: Don't 'kill -9' the postmaster >
In PHP I use this code with a prepared statement to insert null in the field justif_emenda when
the value in the form is an empty string or a string with only spaces. I hope this will help you.
$sql = "INSERT INTO tbl_emenda (" . "id_emenda, subtipo_emenda, tipo_emenda, " . "nome_autor, titulo_autor,
login_autor," . "cod_acao_gov, nome_acao_gov, " . "texto_emenda, justif_emenda) " .
"VALUES(?,?,?,?,?,?,?,?,?,nullif(trim('' from ?),''))";
$stmt = $conn->Prepare($sql);
if (!$conn->Execute($stmt, array( $id_emenda, 'S', 'Aditiva', $_SESSION['nome_autor'],
$_SESSION['titulo_autor'], $_SESSION['login_autor'], $_REQUEST['selCodAcaoGov'],
$_REQUEST['txtNomeAcaoGov'], $_REQUEST['txtTexto'], $_REQUEST['txtJustif'] ))) { erroSQL($conn, $sql);
die("Erro..");
}
_______________________________________________________
Yahoo! Acesso Grátis - Internet rápida e grátis.
Instale o discador agora! http://br.acesso.yahoo.com/
On Aug 24, 2005, at 1:05 AM, Matt A. wrote:
> We used nullif('$value','') on inserts in mssql. We
> moved to postgres and love it but the nullif() doesn't
> match empty strings to each other to return null other
> than a text type, causing an error. This is a major
> part of our application.
I *certainly* hope you're not passing $value in straight from your
web form directly into the SQL. You're opening yourself up for SQL
injection attacks.
Why not just have your app that reads the form generate the proper
value to insert? That is the safe route.
Vivek Khera, Ph.D.
+1-301-869-4449 x806