How to insert DECIMAL into MySQL database

Question

I have a database table with some fields  one of them  cost  is set to the DECIMAL data type  I set the parameters to 4 2  which should allow 4 numbers before the decimal point  and 2 afterwards    I ve been told that the 4 here is the total amount  and the 2 is the amount after the decimal  could somebody please clear that up on a side note    When I insert data via a POST request  the value 3 80 for example  the data stored to my database is actually 99 99   What am I doing wrong to cause this   Here s the table   CREATE TABLE IF NOT EXISTS  mytable     id  int 11  NOT NULL AUTO INCREMENT   title  varchar 256  NOT NULL   cost  decimal 4 2  NOT NULL  PRIMARY KEY   id       Here s my add query   INSERT INTO mytable   id   title   cost    VALUES  0   test title    3 80     Update  It works after I changed 4 2 to 6 2

User · Answer

Yes  4 2 means  4 digits total  2 of which are after the decimal place   That translates to a number in the format of 00 00  Beyond that  you ll have to show us your SQL query  PHP won t translate 3 80 into 99 99 without good reason  Perhaps you ve misaligned your fields values in the query and are trying to insert a larger number that belongs in another field

User · Answer

I noticed something else about your coding     look  INSERT INTO reports services  id title description cost  VALUES  0   test title    test decription     3 80     in your  CREATE TABLE  code you have the id set to  AUTO INCREMENT  which means it s automatically generating a result for that field      but in your above code you include it as one of the insertions and in the  VALUES  you have a 0 there    idk if that s your way of telling us you left it blank because it s set to AUTO INC  or if that s the actual code you have    if it s the code you have not only should you not be trying to send data to a field set to generate it automatically  but the RIGHT WAY to do it WRONG would be    0     you put  0    lol    so that might be causing some of the problem     I also just noticed in the code after  test description  you have a space before the       that might be throwing something off too     idk   I hope this helps n maybe resolves some other problem you might be pulling your hair out about now     speaking of which     I need to figure out my problem before I tear all my hair out      good luck       UPDATE       I almost forgot    if you have the 0 there to show that it s blank    you could be entering  test title  as the id and  test description  as the title then  3 whatever cents  for the description leaving  cost  empty       which could be why it maxed out because if I m not mistaking you have it set to NOT NULL     and you left it null    so it forced something    maybe     lol

User · Answer

MySql decimal types are a little bit more complicated than just left-of and right-of the decimal point   The first argument is precision  which is the number of total digits  The second argument is scale which is the maximum number of digits to the right of the decimal point   Thus   4 2  can be anything from -99 99 to 99 99   As for why you re getting 99 99 instead of the desired 3 80  the value you re inserting must be interpreted as larger than 99 99  so the max value is used  Maybe you could post the code that you are using to insert or update the table   Edit  Corrected a misunderstanding of the usage of scale and precision  per http   dev mysql com doc refman 5 0 en numeric-types html

[mysql] How to insert DECIMAL into MySQL database

Examples related to mysql

Examples related to database

Examples related to database-design

Examples related to decimal