Solved

MySQL decimal field is rounding and shouldn't be

Posted on 2016-09-06
12
41 Views
Last Modified: 2016-09-06
Hi there! I have a MySQL table that includes the following table column:

	percentDegree	decimal(3,1)	Not NULL	Default 0.0

Open in new window


When I try to run an update...

UPDATE tbl_degrees SET percentDegree = '76.9' WHERE dataDate = '2015'

Open in new window


...the 76.9 is entered in the table as 77.0.

I've seen other questions like this on here but it's usually a matter of people not having and decimal places defined or using the INT type. I've also seen a comment that "you can use FLOAT, but that comes with its own problems" but can't seem to find what those problems would be. Also, isn't this exactly the data type you would use decimals for?

Thanks!
0
Comment
Question by:SiobhanElara
  • 5
  • 4
  • 2
  • +1
12 Comments
 
LVL 49

Expert Comment

by:Ryan Chong
ID: 41786176
what about:

UPDATE tbl_degrees SET percentDegree = 76.9 WHERE dataDate = '2015'

?
1
 
LVL 22

Expert Comment

by:Kim Walker
ID: 41786192
How are you viewing the value that's stored in the table? Are you querying the table or are you browsing the table in phpMyAdmin or a similar MySQL management utility?

If you're querying the table, it's possible that it's getting rounded somehow before it's displayed.
0
 
LVL 49

Expert Comment

by:Ryan Chong
ID: 41786193
>>the 76.9 is entered in the table as 77.0
I just did a test, both SET percentDegree = '76.9' or SET percentDegree = 76.9 are working fine. they didn't turn to 77.0

more towards a display issue for me?
0
 
LVL 52

Expert Comment

by:Julian Hansen
ID: 41786255
Confirm both '76.9' and 76.9 on a decimal(3,1) works as expected.

Where are you seeing 77.0
0
 

Author Comment

by:SiobhanElara
ID: 41786270
It's both displaying that way and in the database that way; I'm also viewing the table through phpMyAdmin. Here's a screenshot of a query, the form, and what's in the database. (I'd entered 26.7 as seen in the query; the area under that is the result. This is a slightly different query than the original question, but all tables are having this problem.)

decimal-wonkyness.jpg
0
 
LVL 52

Expert Comment

by:Julian Hansen
ID: 41786291
And if you run this directly in PHPMyAdmin
UPDATE data_educational_attainment SET percentBachelor=26.7 WHERE countryID=0 AND dataDate='2016';

Open in new window

0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:SiobhanElara
ID: 41786307
That works, as does double-clicking on the cell on phpMYAdmin table view and entering it there.
0
 
LVL 52

Expert Comment

by:Julian Hansen
ID: 41786316
The problem is then in how you are submitting it to the DB - what is running that query?
0
 

Author Comment

by:SiobhanElara
ID: 41786350
The ColdFusion code is as follows:

<cfquery datasource="#APPLICATION.ds#" result="bloop">
UPDATE #FORM.tableName#
SET #FORM.dataName# = <cfqueryparam value="#FORM[i]#" cfsqltype="cf_sql_decimal">
WHERE countyID = <cfqueryparam value="#countyID#" cfsqltype="cf_sql_integer">
</cfquery>

Open in new window


("Bloop" outputs the query shown in the screenshot above.)
0
 
LVL 52

Accepted Solution

by:
Julian Hansen earned 500 total points
ID: 41786366
And if you do this
<cfquery datasource="#APPLICATION.ds#" result="bloop">
UPDATE #FORM.tableName#
SET #FORM.dataName# = <cfqueryparam value="#FORM[i]#" scale="1" cfsqltype="cf_sql_decimal">
WHERE countyID = <cfqueryparam value="#countyID#" cfsqltype="cf_sql_integer">
</cfquery>

Open in new window

I don't use ColdFusion but according to the docs - the omission of the scale parameter defaults the value to 0 decimal places.
0
 

Author Closing Comment

by:SiobhanElara
ID: 41786416
Holy crap, I hang my head in shame. Thank you for pointing out what I should have known all along!
0
 
LVL 52

Expert Comment

by:Julian Hansen
ID: 41786467
You are welcome.
0

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Run SQL statement in Microsoft Access 9 32
Have issues with Query MySQL 9 54
poor performance from  MySQL stored procedure 6 23
simple shopping cart database structure 5 26
Both Easy and Powerful How easy is PHP? http://lmgtfy.com?q=how+easy+is+php (http://lmgtfy.com?q=how+easy+is+php)  Very easy.  It has been described as "a programming language even my grandmother can use." How powerful is PHP?  http://en.wikiped…
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…
I designed this idea while studying technology in the classroom.  This is a semester long project.  Students are asked to take photographs on a specific topic which they find meaningful, it can be a place or situation such as travel or homelessness.…

948 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now