how to store decimal values in MYSQL

[digitman]

Hi.

I have a field that is of type INT(100). However whenever I try to store decimal values in the field only the number to the left side of the decimal is stored. For example if I run the query:

INSERT INTO mytable (myfield) VALUES('249.50')

only the number 249 will be stored and the .50 part will be dropped. What am I doing wrong here?

Thanks.

[MikeBigg]

The part to the left of the decial is the INTeger part of the number. You are using a INT field which by definition will only store the bit to the left of the ".".

Try a float field.

Mike

[Daimaju]

use FLOAT, DOUBLE or DECIMAL as types and take a look at http://dev.mysql.com/doc/mysql/en/Column_types.html

btw i think an INT(100) is way to great, i don't even know the name of that number (1 eith 99 zeros after it)

[swdev]

Or a DECIMAL field. Depends on what you want to do with the data.
Here is the MySQL Numeric Types information

[MikeBigg]

btw i think an INT(100) is way to great, i don't even know the name of that number (1 eith 99 zeros after it)

its called a googol

Mike

[stereofrog]

Well, actually "100" in "int(100)" declaration has nothing to do with precision, i.e. how big the number can be. INTs are always 4 bytes, so it's no chance to store "googol" in int field.

As to this topic's question: generally if you know exactly how many decimal places you have, it's better to store numbers as ints, not decimals. For example, "money" values ($249,50) should be stored as cents (24950), not "dollars point cents" (249.50).

[digitman]

hi.

i changed the field to float and it works. However there is another small problem. It drops the left most zero after the decimal point. e.g 249.50 is stored as 249.5. I tried changing the field to double and the problem was still there. The reason I want this to be extremely accurate is that this field will be storing the price of some products, hence if a 90 or 50 cents is stored as 9 or 5 cents it could mean quiet some loss for my client in the long run

anyways, how do I fix this problem?

[Daimaju]

It drops the left most zero after the decimal point. e.g 249.50 is stored as 249.5.
<snip>
hence if a 90 or 50 cents is stored as 9 or 5 cents it could mean quiet some loss for my client in the long run

249.5 is the same as 249.50

So no loss for your client, no problem

If it's because of printing it then take a look at number_format() in the php manual

[Hierophant]

btw i think an INT(100) is way to great, i don't even know the name of that number (1 eith 99 zeros after it)

the (100) has nothing to do with the size you can store. The maximum value that an INT can hold is 2147483647 or 4294967295 if it is unsigned.

The (XX) number only specifies padding available when numbers are zero-padded.

[elel1138]

hi.

i changed the field to float and it works.

Don't use float type to store money amounts!!!

Due to the type's representation in memory, a loss of precision is possible- you can "lose" some cents after operations... this is not very safe, as users can get very angry at every cent...

Store the amount as "amount of cents" in an integer field, or use decimal type (MySQL only, so not recommended), as others already mentioned.

[digitman]

249.5 is the same as 249.50

So no loss for your client, no problem

If it's because of printing it then take a look at number_format() in the php manual

i don't get it. how can be 249 dollars and 5 cents be the same as 249 dollars and 50 cents. may be you could give me an example?

P.S sorry, i'm really really really dumb at maths

[digitman]

Don't use float type to store money amounts!!!

Due to the type's representation in memory, a loss of precision is possible- you can "lose" some cents after operations... this is not very safe, as users can get very angry at every cent...

Store the amount as "amount of cents" in an integer field, or use decimal type (MySQL only, so not recommended), as others already mentioned.

Hi.

can you tell me how I can seperate the amount of cents from the number? It'll take a lot of work if using any formatting function. What do you think about the "double" type? Is there any possibility of loss of precision when using the double type?

thanks.

[Hierophant]

249.5 is not 249 dollars and 5 cents, it is 249 dollars and 50 cents. 249 dollars and 5 cents would be 249.05. Zero has no precision because it has no value so it is dropped if it is the last digit. This continues until a digit with precision is encountered. Welcome to introductory Algebra..

[digitman]

Welcome to introductory Algebra..

rofl .

Ok I've solved this problem, I think. What I'm doing now is to use a decimal type with the length set to 10,2 . This automatically adds 2 numbers after the decimal point, and if no digit is supplied it adds .00. Now 254.50 is also stored as 254.50 and not as 254.5.

I hope I won't have the problem of precision loss with the decimal type. Right?

thank you all for the help

[Hierophant]

No and that is the proper type to use for currency. Depending on what your application is though, you might want to increase your precision to 4 digits and then round it to 2 decimal points in your application.

[elel1138]

What do you think about the "double" type? Is there any possibility of loss of precision when using the double type?

Well, I guess you solved the problem already by using decimal type . About the "double", it's the same as float, but a bit more precise; read about the numerical representation of float numbers for details.

[digitman]

thanks for the help