NULL return 0.00

aarontan78

Hi,

I have a php page that accept user input into a field (decimal value). I set the default value of the field as blank and accept null value.

However, when I insert a null value, it return 0.00. I want the field to return null value to me. What should I do for the field to return null value?

Thanks and regards.

Aaron

Roger_Ramjet

Set the default to NULL for that column and omit any value from the insert query for it. SQL NULL is not the same as php NULL, a mistake which many people make, and that may be your problem here.

aarontan78

Hi,

I have already set the default field no NULL, however I couldn't omit my INSERT statement for that particular field...as the INSERT statement need to retrieve value from that field, and if no value inserted, it will return NULL value.
Any suggestion?

Thanks and regards.

Aaron

Roger_Ramjet

I'm not following you. You want to be able to store NULL in a column - so set it to alloq NULL. You want it to store NULL when no data entered - so set the default value to NULL, otherwise it will default to 0.00 for your decimal field.

Now, when you have no data for any column and want it to just use the column default then you omit that column form your insert query and it will have the column default inserted automatically. If you want to include the column (why when you are building query strings dynamically?) but still insert NULL then you use NULL in the value for that column. I mean you write the word NULL in the query string.

aarontan78

Firstly, thanks for the advice.
I followed your advice:
-allow null value
-setting null as default

However, I received 0.00 when I save the page, in which I want it to be null if nothing is inserted.

My php page consists of INSERT INTO ...VALUE $variable, $variable1 in which $variable1 is text box inserted and saved by user and I want it to accept the text box value and if nothing is inserted, it will return null.

Thanks and regards.

gardnc

if by:

$variable1 is text box inserted and saved by user and I want it to accept the text box value and if nothing is inserted, it will return null.

you mean you want it to insert to the table a NULL if $variable1 has nothing in it. Then if $variable1 when returned empty by the form is not a NULL you need to make a decision prior to the insert and change the $variable1 to NULL, i.e.

if ($variable1 == 0) $variable1 = null;

then do the insert.

aarontan78

Thanks for the reply.

However, the user can also fill in 0 in $variable1, means that I also want it to accept 0 value.
If I put this:
if ($variable1 == 0) $variable1 = null;
then all the 0 value that the user inserted will change to null :bemused:

Any idea how can I change the form or code to accept null value from the form?

Regards