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
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')
It works after I changed 4,2 to 6,2
Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.
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.
(4,2) can be anything from
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.
Corrected a misunderstanding of the usage of scale and precision, per http://dev.mysql.com/doc/refman/5.0/en/numeric-types.html.
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.
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
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.. 🙂
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