CODEWITHSUNDEEP
phpmysql
user547794
user547794

Reputation: 14521

MySQL - Decimal cannot be NULL

Everytime I try to make an insert statement into my dastabase I get a "Incorrect decimal value: 'NULL' for column 'bounty3' at row 1" error. How do I insert a null value into a decimal datatype? Should I just make the default value 0.00?

Incorrect decimal value: '' for column 'bounty3' at row 1 Whole query: INSERT INTO songs (userid, wavURL, mp3URL, genre, songTitle, BPM, insWanted, bounty, insWanted2, bounty2, insWanted3, bounty3, insWanted4, bounty4, insWanted5, bounty5, insWanted6, bounty6, insWanted7, bounty7, insWanted8, bounty8, insWanted9, bounty9, insWanted10, bounty10) VALUES ('12534545', '/audio/wav/jqmrgpfcMichael/135259578210secreason.wav', '/audio/mp3/jqmrgpfcMichael/135259578210secreason.mp3', 'Rock/Funk', 'titlee', '120', 'bass', '20.00', 'guitar', '20.00', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '')

I tried this statement with a NULL value too. Here is the error:

Incorrect decimal value: 'NULL' for column 'bounty3' at row 1 Whole query: INSERT INTO songs (userid, wavURL, mp3URL, genre, songTitle, BPM, insWanted, bounty, insWanted2, bounty2, insWanted3, bounty3, insWanted4, bounty4, insWanted5, bounty5, insWanted6, bounty6, insWanted7, bounty7, insWanted8, bounty8, insWanted9, bounty9, insWanted10, bounty10) VALUES ('12534545', '/audio/wav/jqmrgpfcMichael/143922765110secreason.wav', '/audio/mp3/jqmrgpfcMichael/143922765110secreason.mp3', 'Rock/Funk', 'title', '110', 'bass', '110.00', 'guitar', '20.00', 'NULL', 'NULL', 'NULL', 'NULL', 'NULL', 'NULL', 'NULL', 'NULL', 'NULL', 'NULL', 'NULL', 'NULL', 'NULL', 'NULL', 'NULL', 'NULL')

Upvotes: 12

Views: 33229

Answers (11)

Alex Fernandes
Alex Fernandes

Reputation: 1

It worked por me when POST data is received: ... if ($value == '') {$value = '0.0';} ...

Upvotes: 0

Marius
Marius

Reputation: 190

The field is most likely numeric. If the field is numeric then only numbers are accepted. Empty string is not accepted 

insert into songs (...,bounty3) values(...,'')

neither the NULL string 

insert into songs (...,bounty3) values(...,'NULL')

('NULL' which is just a string, nothing to do with the actual NULL value, so 'NULL' si similar to 'HONEY BEE' if you want).

So if the column is numeric then it will only take numbers. However if the column is NULLABLE then it will accept NULL, too. That means your insert must have

insert into songs (...,bounty3) values (..., NULL)

If you push NULL on such a column and the column has a default value then DEFAULT value will be used instead of the NULL you are pushing.

Upvotes: 1

pilat
pilat

Reputation: 1162

  1. Make sure that the field is "nullable" (does not have NOT NULL in its definition)

  2. Make field's default value NULL.

    Ex.: price decimal(12,2) DEFAULT NULL

  3. Now, to test it, store some number, then '' (an empty string) into this field — it should become NULL in the end.

Upvotes: 1

Brooky
Brooky

Reputation: 31

Another option is to make sure that in your source data, the empty cells or fields contain 'NULL' before you import them in MySQL. This way, MySQL will recognize these fields as being really NULL, and won't transform them to 0's.

Upvotes: 0

Filipe
Filipe

Reputation: 113

Thanks, it worked using NULL when it comes empty:

$conn->bindParam(':'.$valor, ( $key[0] ? $key[0] : NULL ), PDO::PARAM_STR);

Upvotes: 2

Robert Lattery
Robert Lattery

Reputation: 53

Make sure the field type allows for NULL values and that the default is NULL.

Then use:

UPDATE table_name SET date_field=IF('$date_value'='',NULL,'$date_value')

This works for inserts or updates.

I have tried using $date_value = NULL and I have even tried unset($date_value) before inserting or updating, and it never worked on my decimal fields. MySQL always converted them to 0.00. The method above was the only solution that worked for me.

Upvotes: 1

user3249681
user3249681

Reputation: 31

I just dealt with this issue in MySql 5.1.61. If the server is in strict mode, you must enter 0 instead of '' for decimal fields. Disabling strict mode allows you to just do '' to populate a null value.

Upvotes: 3

user557846
user557846

Reputation:

use blank, not null, if you want the default value.

Upvotes: 7

user517491
user517491

Reputation:

Oh.. you are trying to insert an empty string '' into bounty3. Replace it with NULL. I have also noticed empty strings for other possible numeric values eg bonty4. You should replace all empty strings with NULL for numeric values.

eg: mysql_query("INSERT INTO empty_number,number VALUES(NULL,1)");

EDIT: HEY HEY get the point, you cannot insert NULL to a numeric value as 'NULL' because this is a string, you should insert as NULL without any quotation marks

Upvotes: 17

Daniel García
Daniel García

Reputation: 622

You can run this query to allow NULL values in 'bounty3' field.

ALTER TABLE songs CHANGE bounty3 bounty3 DECIMAL(10,0) NULL;

Upvotes: 1

Cameron Skinner
Cameron Skinner

Reputation: 54466

You need to make sure that your database schema allows NULL values for that column. Run describe <tablename>; in your MySQL client to see what the schema for that table is.

If the schema does not allow null then you can use alter table to change the schema. Make sure you don't specify NOT NULL for that column and you should be fine.

Upvotes: 0

Related Questions