[email protected] 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
_______________________________________________
New York PHP User Group Community Talk Mailing List
http://lists.nyphp.org/mailman/listinfo/talk
http://www.nyphp.org/show_participation.php
