SitePoint Sponsor

User Tag List

Results 1 to 17 of 17
  1. #1
    FBI secret agent digitman's Avatar
    Join Date
    Sep 2004
    Location
    Work
    Posts
    697
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    how to store decimal values in MYSQL

    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.

  2. #2
    SitePoint Guru MikeBigg's Avatar
    Join Date
    Jun 2004
    Location
    Reading, UK
    Posts
    970
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  3. #3
    SitePoint Evangelist
    Join Date
    May 2004
    Location
    Germany
    Posts
    550
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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)

  4. #4
    SitePoint Wizard swdev's Avatar
    Join Date
    Oct 2004
    Location
    UK
    Posts
    1,053
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Or a DECIMAL field. Depends on what you want to do with the data.
    Here is the MySQL Numeric Types information

  5. #5
    SitePoint Guru MikeBigg's Avatar
    Join Date
    Jun 2004
    Location
    Reading, UK
    Posts
    970
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Daimaju
    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

  6. #6
    SitePoint Wizard stereofrog's Avatar
    Join Date
    Apr 2004
    Location
    germany
    Posts
    4,324
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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).

  7. #7
    FBI secret agent digitman's Avatar
    Join Date
    Sep 2004
    Location
    Work
    Posts
    697
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?

  8. #8
    SitePoint Evangelist
    Join Date
    May 2004
    Location
    Germany
    Posts
    550
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by digitman
    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

  9. #9
    Your Lord and Master, Foamy gold trophy Hierophant's Avatar
    Join Date
    Aug 1999
    Location
    Lancaster, Ca. USA
    Posts
    12,305
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Daimaju
    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.
    Wayne Luke
    ------------


  10. #10
    Woof! Woof! elel1138's Avatar
    Join Date
    Apr 2004
    Location
    Warsaw, Poland
    Posts
    202
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by digitman
    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.
    Leszek Leszczyński
    www.hazelsoft.com
    --- Real programmers don't document. If it was hard to write,
    it should be hard to understand. ---

  11. #11
    FBI secret agent digitman's Avatar
    Join Date
    Sep 2004
    Location
    Work
    Posts
    697
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Daimaju
    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

  12. #12
    FBI secret agent digitman's Avatar
    Join Date
    Sep 2004
    Location
    Work
    Posts
    697
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by elel1138
    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.

  13. #13
    Your Lord and Master, Foamy gold trophy Hierophant's Avatar
    Join Date
    Aug 1999
    Location
    Lancaster, Ca. USA
    Posts
    12,305
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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..
    Wayne Luke
    ------------


  14. #14
    FBI secret agent digitman's Avatar
    Join Date
    Sep 2004
    Location
    Work
    Posts
    697
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Hierophant
    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

  15. #15
    Your Lord and Master, Foamy gold trophy Hierophant's Avatar
    Join Date
    Aug 1999
    Location
    Lancaster, Ca. USA
    Posts
    12,305
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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.
    Wayne Luke
    ------------


  16. #16
    Woof! Woof! elel1138's Avatar
    Join Date
    Apr 2004
    Location
    Warsaw, Poland
    Posts
    202
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by digitman
    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.
    Leszek Leszczyński
    www.hazelsoft.com
    --- Real programmers don't document. If it was hard to write,
    it should be hard to understand. ---

  17. #17
    FBI secret agent digitman's Avatar
    Join Date
    Sep 2004
    Location
    Work
    Posts
    697
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks for the help


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •