dealing with potential null values

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
rhaynes
Forum Newbie
Posts: 18
Joined: Mon Nov 08, 2004 8:32 am

dealing with potential null values

Post 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
rehfeld
Forum Regular
Posts: 741
Joined: Mon Oct 18, 2004 8:14 pm

Post by rehfeld »

Code: Select all

if (isSet($_POST['var1'])) {
    $var1 = $_POST['var1'];
} else {
    $var1 = '';
}

// or use ternary style

$var1 = (isSet($_POST['var1'])) ? $_POST['var1'] : '';
crabyars
Forum Commoner
Posts: 37
Joined: Thu Jun 17, 2004 8:24 pm

Post 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..
rhaynes
Forum Newbie
Posts: 18
Joined: Mon Nov 08, 2004 8:32 am

using quotes for integer variables??

Post 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
crabyars
Forum Commoner
Posts: 37
Joined: Thu Jun 17, 2004 8:24 pm

Post 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 ;)
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post 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 . ")";
rhaynes
Forum Newbie
Posts: 18
Joined: Mon Nov 08, 2004 8:32 am

query doesn't like quotes around integers....

Post 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) &#123;
                print("Connection Failed.");
                exit;
        &#125;
$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
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post 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', ";
}
Post Reply