fieldtype decimal empty if no value...

[glennn.php]

i'm not quite up on when to apply NULL and when not to, and i really want my decimal (5,2) fields to show NO value if there's not an actual value - when i ran a large sql INSERT all my empty fields were converted to '0.00' and phpmyadmin won't let me delete those values...

 

won't decimal allow empty fields?

[ignace]

That is because your decimal field currently has NOT NULL change it to NULL that way it allows you to have NULL as a value

[glennn.php]

they're ALL NULL already - when i try to delete the fields with 0.00 the error is "#1366 Incorrect decimal value: '' for column 'P-15' at row 1" (it's set to decimal(5,2))

[fenway]

Then check the actual value.

[ignace]

Try to update the field and set it's value explicit to NULL

[glennn.php]

"Warning: #1366 Incorrect decimal value: 'NULL' for column 'P-15' at row 1"

 

is decimal(5,2) the reason? perhaps there's a better setting for this fieldtype? i spose i could just set it to varchar (11) and be done with it...?

 

 

Try to update the field and set it's value explicit to NULL

[Mchl]

Make sure you set them to NULL not 'NULL', as the one with quotes is recognised as a string value.

In phpMyAdmin's edit form you have a special checkbox to mark field as NULL. You don't enter the word NULL into value box.

[glennn.php]

Make sure you set them to NULL not 'NULL', as the one with quotes is recognised as a string value.

In phpMyAdmin's edit form you have a special checkbox to mark field as NULL. You don't enter the word NULL into value box.

 

i've overlooked that checkbox for i dunno how long... thanks - i only have about fields that are 0.00 -

 

is there an UPDATE table SET ''any field" = NULL where "any field" = '0.00'; ?

 

thanks for your help.

 

G

[Mchl]

It should be:

UPDATE table SET `any field` = NULL where `any field` = 0; 

 

BTW: you're not actually using spaces in field names, do you?

[glennn.php]

It should be:

UPDATE table SET `any field` = NULL where `any field` = 0; 

 

BTW: you're not actually using spaces in field names, do you?

 

) - no, 'any field' meant 'any field throughout the table' instead of SET 'field_name' = NULL - i'm looking for a wildcard for all the fields, because the only ones that contain 0.00 would be the ones i don't want in there.

 

what happened is i converted an excel file to an sql file and the query turned all the empty fields into 0.00 where i had it set to decimal. i'd rather not reconvert the entire thing again.

[Mchl]

No, there's no field wildcard because usually you don't need anything like it.

[glennn.php]

No, there's no field wildcard because usually you don't need anything like it.

 

didn't think so - i'll just do it column by column then. thanks for your  help

[Mchl]

Actually, if there are many columns, you can use PHP to help you with this.

 

Use

SELECT 
  COLUMN_NAME 
FROM 
  information_schema.columns 
WHERE 
  TABLE_SCHEMA = 'yourDatabase'
  AND TABLE_NAME = 'yourTable'
  AND DATA_TYPE = 'decimal'
  AND IS_NULLABLE = 'YES'

to get list of DECIMAL fields in this table, that can be set to null. Having that, you can write PHP script, that will update all of them the way you like.