dealing with potential null values
Moderator: General Moderators
dealing with potential null values
Hi, I have a html form which I use to enter data which is then used in a
insert into
command to enter the data into a postgresql database.
The insert into command doesn't seem to like when some of the variables don't have data (i.e. I have not entered anything into the box). I have got around this before by testing for null values and then changing the query. In this application there are too many combinations of variables which may be null. I would like to keep the query the same regardless if some of the variables are null. I know that in the psql shell I can enter "NULL" (with no quotes) for certain variables and have no data entered. I tired something like: (I do know that at least one of the variables will not be NULL...)
<?php
if (!$var1)
{ $var1=NULL}
if (!$var2)
{ $var2=NULL}
if (!$var3)
{$var3=NULL}
query = "insert into table (var1,var2,var3) values ($var1,$var2,$var3)";
$result = pg_exec(query,$connection)
But it didn't seem to work, any suggestions?
Thanks,
R Haynes
insert into
command to enter the data into a postgresql database.
The insert into command doesn't seem to like when some of the variables don't have data (i.e. I have not entered anything into the box). I have got around this before by testing for null values and then changing the query. In this application there are too many combinations of variables which may be null. I would like to keep the query the same regardless if some of the variables are null. I know that in the psql shell I can enter "NULL" (with no quotes) for certain variables and have no data entered. I tired something like: (I do know that at least one of the variables will not be NULL...)
<?php
if (!$var1)
{ $var1=NULL}
if (!$var2)
{ $var2=NULL}
if (!$var3)
{$var3=NULL}
query = "insert into table (var1,var2,var3) values ($var1,$var2,$var3)";
$result = pg_exec(query,$connection)
But it didn't seem to work, any suggestions?
Thanks,
R Haynes
Code: Select all
if (isSet($_POST['var1'])) {
$var1 = $_POST['var1'];
} else {
$var1 = '';
}
// or use ternary style
$var1 = (isSet($_POST['var1'])) ? $_POST['var1'] : '';try putting single quotes around your input vars, for example:
Then you should be able to pass a true null var. Also you wont need to check for nulls if you use the single quotes.
BTW, you need a $ in front of your query statement (but that was probably a typo in the post yeah?)
rehfeld, I might be missing something but I'm not sure your code frag will help with his example..
Code: Select all
$query = "insert into table (var1,var2,var3) values ('$var1','$var2','$var3')";BTW, you need a $ in front of your query statement (but that was probably a typo in the post yeah?)
rehfeld, I might be missing something but I'm not sure your code frag will help with his example..
using quotes for integer variables??
Hi, var1,var2,var3 contain integers (if they contain data...) Will using single quotes around the variables mess things up?crabyars wrote:try putting single quotes around your input vars, for example:
Then you should be able to pass a true null var. Also you wont need to check for nulls if you use the single quotes.Code: Select all
$query = "insert into table (var1,var2,var3) values ('$var1','$var2','$var3')";
BTW, you need a $ in front of your query statement (but that was probably a typo in the post yeah?)
rehfeld, I might be missing something but I'm not sure your code frag will help with his example..
Thanks,
R Haynes
I always build my queries from a $what array. This array contains 'field' as key, and 'value' as value. (vs var1, var2, ...)
Code: Select all
protected function dbmsCreate($what)
{
// build the query
$q1 = "INSERT INTO $this->table (";
$q2 = ") VALUES (";
foreach($what as $attribute => $value)
{
$q1 .= "$attribute, ";
if (is_null($value))
{
$q2 .= "NULL, ";
}
else
{
$value = mysql_real_escape_string($value, $this->db);
$q2 .= "'$value', ";
}
}
$q1 = rtrim($q1, ", ");
$q2 = rtrim($q2, ", ");
$query = $q1 . $q2 . ")";query doesn't like quotes around integers....
Putting quotes around the potentially null valued integer variables yields the following error:crabyars wrote:Hi, var1,var2,var3 contain integers (if they contain data...) Will using single quotes around the variables mess things up?I think it should be fine, I use single quotes for my integer fields.. Give it a try to be sure and let us know
Warning: pg_exec(): Query failed: ERROR: invalid input syntax for integer: "" . in /var/www/html/cards/add-entrynew.php on line 12
Here is the offending code snippet, some of the quantity and value variables may be null (they are sent via a html form)...
Code: Select all
<?php
$connection = pg_connect("dbname=hockey1 user=rhaynes host=rhaynes.acadiau.ca");
if (!$connection) {
print("Connection Failed.");
exit;
}
$query = "insert into tradelistnew (year,manufacturer,set,card_number,player_name,quantitynrmt,quantityexmt,quantityex,quantityvg,quantitypfg,valuenrmt,valueexmt,valueex,valuevg,valuepfg) values ('$year','$manu','$set','$card_number','$player','$quantitynrmt','$quantityexmt','$quantityex','$quantityvg','$quantitypfg','$valuenrmt','$valueexmt','$valueex','$valuevg','$valuepfg')";
$myresult = pg_exec($connection,$query);
?>Any ideas?
Thanks,
R Haynes
as you can see in the snipped i posted
Code: Select all
if (is_null($whatever_friggin_var))
{
$query .= "NULL, ";
}
else
{
$query . = "'$whatever_friggin_var', ";
}