Help with NULL values

[Master_Zen]

Hello guys,

here's a snippet from one of my php files:

	if ($list['PRODUCT'] >= 0) {
		$product = number_format ($list['PRODUCT']);
	} elseif ($list['PRODUCT'] == NULL) {
		$product = '?';
	}

I want the page to display a ? sign if the value in the mySQL table is NULL (which it clearly shows as NULL) yet it ends up displaying 0 all the time. If I change the >= to just >, then it displays correctly.

I thought 0 was something entirely different than NULL.

The mySQL field is INT(8) with NULL set to yes, and with 0 as the default.

[Master_Zen]

btw, I've been googling to find an answer, I read something about PHP not being able to distinguish between integer values of 0 and NULL. Is that true? If so, what can I use to fix it other than changing from INT to VARCHAR?

EDIT: Heh, changing it to VARCHAR did NOT work... :p

[mrhappiness]

Master Zen wrote:

btw, I've been googling to find an answer, I read something about PHP not being able to distinguish between integer values of 0 and NULL. Is that true? If so, what can I use to fix it other than changing from INT to VARCHAR?

[man]is_null[/man], [man]gettype[/man]?

[Master_Zen]

echo 'this variable is ' . gettype($list['PRODUCT']);

RESULT:

NULL

I am stumped 🙁

[Roger_Ramjet]

Use 'IS NULL' as in

if (is_null($list['PRODUCT'])) {

just as Mr Happines said. RTFM my son RTFM

[TobyRT]

my inclination would be to add along the lines of

if ($product == 0){ echo '?';}

But that's just cos I haven't got my head around NULL yet either

makes note to rtfm

[Roger_Ramjet]

In computing, NULL just means missing/not there. That is why in some circumstances it equates to false. But it is not the same as false - cos false still exists, while null does not.

Very important and usefull concept in databases: in fact essentail to master for advanced sql.

[Master_Zen]

Still doesn't work

	if ($list['PRODUCT'] >= 0) {
		$product = number_format ($list['PRODUCT']);
	} elseif (is_null($list['PRODUCT'])) {
		$product = '?';
	}

echoing is_null($list['PRODUCT']) results in a display of "1". I take that as being True? If so, then why the hell doesn't the code work correctly?

[Master_Zen]

Well, I've tried everything now and nothing works. I've used === instead of ==. I've added an && $list['PRODUCT'] !== 0 to the elseif statement. NOTHING works. Seems like PHP has just decided to make 0 and NULL the exact same thing.

[Roger_Ramjet]

Try this

if (is_null($list['PRODUCT'])) {
        $product = '?';
} else {
       $product = number_format ($list['PRODUCT']); 
}

[Master_Zen]

nope, didn't work. 🙁

[Roger_Ramjet]

Well then there is something wrong with your data or query. If you have default set to 0 then that is probably what you get, not null

[Master_Zen]

well, problem solved, alas it was something different than I had expected.

The main problem was, a code I had overlooked in another file which is linked to this one and which is the one which actually displays the $product variable. Problem was that on that file I had used number_format too, so the two number_formats were overlapping and causing a big problem. I did not notice visually because in the page I was working on, the value was null or 0. I noticed the problem when I loaded another page where the value was 15,000 and the overlap was causing it to dispaly just 15.

By eliminating the overlap I was finally able to get NULL to be recognized. However, I still would have had the problem if I didn't apply Roger Ramjet's last suggestion in putting the NULL statement first in the if(). That was what worked. Curiously changing the default settings on mySQL had no effect, even if the default is 0, which is good since the nature of my data means i'd rather have a default of 0 than NULL.

All in all, thanks to everyone for their suggestions, particularly Roger Ramjet. I really appreciate it.

[Roger_Ramjet]

You could also have done

if (!is_null($list['PRODUCT'])) { 
       $product = number_format ($list['PRODUCT']); 
} else {
        $product = '?';
}

as i said NULL means 'an absence of value' ie does not exist, so should be the primary test.

Glad we could help.

[Master_Zen]

this was the code that ultimately worked:

	 if ($list['PRODUCT'] === NULL) { 
			$product = '?'; 
		} elseif ($list['PRODUCT'] >= 0) { 
			$product = number_format ($list['PRODUCT']); 
		}