NYCPHP Meetup

[David Krings]

lists at nopersonal.info wrote:
> 
> Okay, wait--but what about what Dan said re NULL values not being added
> to averages, making them useful statistically? In that case wouldn't you
> want NULL to stay NULL? IOW, in his case the query would would only
> retrieve values WHERE foo != NULL? But then the manual says that the
> arithmetic comparison operators won't work with NULL, so that can't be
> right...

Dan mentioned a case where having NULL is of good use, because in his example 
it makes a difference if the person responding to a survey doesn't answer the 
question or answers it with 0. If there is no answer you cannot treat it the 
same way as 0. It is an example that I didn't think about.
What I was talking about was in regards to variables in PHP being NULL. The 
"retrieve values WHERE foo != NULL" is SQL. That is a different language.


> Hang on, let me think a minute... Okay, so then I guess you'd just
> assign a value of 0 or '' to anything that was NULL, and then have PHP
> only calculate numbers where $foo >= 1? But if that's the case, then why
> use NULL in the first place? That can't be right, so I must still be
> missing some important point.

Just because I couldn't come up with a case for using NULL (and I tried to 
find one) doesn't mean there is one. Dan is right and his example is spot on. 
My explanation wasn't wrong, but incomplete.


> One last try: Maybe the query would be along the lines of SELECT * WHERE
> foo NOT IN NULL, that way there wouldn't be any need to deal with it
> because since it was never retrieved in the first place...?

I fired up my rusty PHP IDE and tried a few things. Turns out that SQL is way 
more picky about what NULL is, whereas PHP considers NULL often as 0 or an 
ampty string. For example, when you have this in PHP
<?php
$a = "";
$b = NULL;
if ($a == $b) echo "they are the same";
?>

you will see "they are the same" as output. In SQL when your run
SELECT * FROM Table WHERE Field = NULL
and then
SELECT * FROM Table WHERE Field = ''

you may very well end up with two different results. I think it has to do with 
the fact that PHP doesn't take it too serious with the type of variable. For 
example
<?php
$a = 12;
$b = "threefour";
echo $a.$b;
?>

gives you this output: 12threefour

PHP just makes the integer into a string and sticks both together. In other 
languages this throws an error, because the variables are not of the same 
type. That said, for SQL searching for something that is NULL isn't the same 
as searching for something that is 0.
PHP allows for the more picky way in comparison by using three equal signs. 
That means that
<?php
$a = "";
$b = NULL;
if ($a === $b) echo "they are the same";
?>

won't output anything. Only if you set $a to NULL as well you will see text as 
output. It took me a while and a few tries to remember how PHP treats NULL. It 
basically is the same as not assigning any value or unsetting a variable or 
array (which seems to be a quick way to figure out if an array is empty). For 
example
<?php
echo $x + 23;
?>
outputs 23.

So, what I propose you keep in mind is that PHP knows about NULL, but treats 
it as if it is 0 for numerical variables or as an empty string. In SQL it is 
not the case. If PHP's behaviour is right or wrong can be hotly debated. I 
like that it doesn't take it too serious with the variable type, but just to 
assume that some undefined variable is out of a sudden 0 is quite bold. But 
that's the way it is and when one knows about it one can deal with it.

> Sorry for making you witness my somewhat scattershot thinking process,
> but even the smallest sequences of programming logic can still be a big
> challenge for me.

See, I hate programming and programming hates me. I do like programming in 
PHP, because PHP isn't so anal about stuff like this as Java or C. That makes 
me think less. The problem is that you might end up with really bad results. 
For example when no submission is an error condition, but submitting an empty 
string or 0 is fine. In that case having NULL be the same as zero is not what 
you want. So while for most cases PHP's assumptions work out fine I wouldn't 
count on PHP being always right.


>> Sure, one could say that NULL is the same as 0 or "", but that is a
>> purely arbitrary interpretation, although maybe a convenient one.
> 
> Got it.

See above, it is very convenient, because sqrt(NULL) gives 0 and not an error.


>> I hope I explained it in an understandable way.
> 
> You did an awesome job of explaining. Thanks again for taking the time
> to help.

You are welcome, just keep in mind that PHP and SQL aren't the same, even when 
we often mush them together in code.

David