null decimal

[5kyy8lu3]

Hi.  I have PHP 5.0.45.

 

I have a column called windchill in my table and the data type is set to decimal(5,2) null yes.  It seems when i insert an empty variable into that column, it saves at 0.00.  Since a temperature of 0 degrees can occur, that won't work.  Is that 0.00 distinguishable from null?  How can I enter a null value and tell if it's the number 0 or if it's null when I select data from that column?  Thanks!

[rhodesa]

For Null

INSERT INTO tableName (windchill) VALUES (NULL)
SELECT * FROM tableName WHERE windchill IS NULL

For 0

INSERT INTO tableName (windchill) VALUES (0)
SELECT * FROM tableName WHERE windchill = 0

[5kyy8lu3]

For Null

INSERT INTO tableName (windchill) VALUES (NULL)
SELECT * FROM tableName WHERE windchill IS NULL

For 0

INSERT INTO tableName (windchill) VALUES (0)
SELECT * FROM tableName WHERE windchill = 0

 

I'm not sure I follow.

 

If I try to enter a value of NULL it still shows up in the table as 0.00 in my table.

If I want to enter a temperature of 0 degrees, it also shows up as 0.00 in the table.

 

I have a page that loads * from that table, and prints those values out in a while loop.  If the column is NULL for a row, I want to be able to know so I can print "N/A" for that row.  If the column has the actual temperature of zero, I want it to properly print 0 degrees to the browser.  What can I do to tell the difference?  I've read mysql.com and my o'reilly book but I guess I'm just not comfortable with NULL values and how they work with decimal data types. (or at all)

 

Thanks.

[5kyy8lu3]

I still can't figure this out.  The only thing I can think of doing is setting the data type to string so it doesn't default to 0.00 when I want the column to be empty for a row.

 

Incase I'm not being clear enough, let me reword this.

 

I'm going to insert two new rows to my table.  For the first row, I want to enter a value of zero in the windchill column.  $wc = 0;

 

In the second row, I don't have windchill information so I want it to be null.  $wc = ''; or $wc = 'NULL'; or however I'm supposed to do it.

 

Using the decimal(5,2) data type both entires are showing up as 0.00 and I can't tell them apart when I pull them back out and it's really important to know since having no data (null) vs having 0 as a value in the column are two completely different things.

 

So... if I pull both rows out of the database and print them to the browser, the first row should print 0 degrees.  The second row is supposed to have a null value for the windchill column so it prints 'N/A' to the browser.

 

How can I do this with a decimal data type?  Is this possible?  I would imagine it's possible, I just have no idea how and all the searching and reading online via google searching, looking up NULL and DECIMAL in my book, and even forum searches haven't given me sufficient information to figure this out on my own

[peddel]

just add an if clause for when $wc is entered it enters 'N/A'

use a varchar(10) to store both string and ints

 

Its something that works

[5kyy8lu3]

just add an if clause for when $wc is entered it enters 'N/A'

use a varchar(10) to store both string and ints

 

Its something that works

yea that was kind of my last resort, i figured there was some 'proper' method of getting around this but I might as well go this route until i figure something out lol thanks

[rhodesa]

not sure what you are doing wrong...works fine for me:

 

CREATE TABLE temps (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
  windchill DECIMAL( 5, 2 ) NULL
);
INSERT INTO temps ( windchill ) VALUES ( 5 );
INSERT INTO temps ( windchill ) VALUES ( 12.2 );
INSERT INTO temps ( windchill ) VALUES ( 0 );
INSERT INTO temps ( windchill ) VALUES ( NULL );

SELECT * FROM temps

id windchill

1 5.00

2 12.20

3 0.00

4 NULL

[Zane]

just in case you didn't know and/or haven't done it already

 

you have to enable null values to be used on a column.  as rhodesa did.