Page 1 of 1
dealing with potential null values
Posted: Sun Nov 21, 2004 8:26 pm
by rhaynes
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
Posted: Sun Nov 21, 2004 8:34 pm
by rehfeld
Code: Select all
if (isSet($_POST['var1'])) {
$var1 = $_POST['var1'];
} else {
$var1 = '';
}
// or use ternary style
$var1 = (isSet($_POST['var1'])) ? $_POST['var1'] : '';
Posted: Sun Nov 21, 2004 8:35 pm
by crabyars
try putting single quotes around your input vars, for example:
Code: Select all
$query = "insert into table (var1,var2,var3) values ('$var1','$var2','$var3')";
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..
using quotes for integer variables??
Posted: Sun Nov 21, 2004 8:58 pm
by rhaynes
crabyars wrote:try putting single quotes around your input vars, for example:
Code: Select all
$query = "insert into table (var1,var2,var3) values ('$var1','$var2','$var3')";
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..
Hi, var1,var2,var3 contain integers (if they contain data...) Will using single quotes around the variables mess things up?
Thanks,
R Haynes
Posted: Sun Nov 21, 2004 11:02 pm
by crabyars
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

Posted: Mon Nov 22, 2004 2:16 am
by timvw
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....
Posted: Mon Nov 22, 2004 7:05 am
by rhaynes
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

Putting quotes around the potentially null valued integer variables yields the following error:
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);
?>
Without the single quotes it complains about a syntax error just before the first null value. I do know that at least one of the quantity variables contains data. Because of the number of variables I don't want to do a mess of if statements to custom build the query to fit the situation. I would just like to make the query with all the variables so that if one or more of the variables happens to be null then null data is inserted in the database.
Any ideas?
Thanks,
R Haynes
Posted: Mon Nov 22, 2004 8:20 am
by timvw
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', ";
}