Rounding in php / MySQL

I have a web app we are building; one aspect involves rounding.

The database has several field that are float, format 5,2.

If the value going in on an INSERT or UPDATE is .125, it rounds to .13. If I use the php round function it gives (to 2 places) .12 (on the SAME value).

I'm a math major but I can't remember the math rule; been too many years.

This implies that rounding is done differently in php & MySQL.

Is that true?

Thanks
Richard KortsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

GaryCommented:
5 and above round up else round down.
So MySQL is correctly rounding the number to the nearest decimal place.
PHP would also round it to 0.13 as well unless you are specifying the rounding type.
0
Richard KortsAuthor Commented:
I am using this:  round(floatval($var),2).

I am using floatval because the data is coming in from a .txt file, tab delimited Excel.
0
GaryCommented:
That shouldn't matter. What if you separate the functions - what does the floatval part return?
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

Dave BaldwinFixer of ProblemsCommented:
What the original $val look like when the rounding doesn't work right?

It appears that PHP has it's own rules and limits.  Note the note about strings with commas.  http://us2.php.net/manual/en/function.round.php
0
COBOLdinosaurCommented:
PHP float is going to truncate so it is correct for the function being used.  By the time the round function gets the value, there is nothing to round.

Cd&
0
Richard KortsAuthor Commented:
To COBOLdinosaur

How does php floatval know to truncate it to two places after the decimal? It has no idea of what I am later going to do.
0
Dave BaldwinFixer of ProblemsCommented:
With 'floats' in PHP, there are all kinds of things that can go 'wrong'.  Including the fact that the internal representation is not exact for most numbers.  http://us1.php.net/manual/en/language.types.float.php

 And PHP does not do 'decimal arithmetic', it always converts to binary floats, but I believe that MySQL does.  http://dev.mysql.com/doc/refman/5.5/en/fixed-point-types.html
0
Richard KortsAuthor Commented:
So maybe I should leave the floatval out & just round the raw text value?
0
Richard KortsAuthor Commented:
Of course the whole problem is much bigger than this; I'm just trying to narrow it down.

Basically, I am trying to see if a potential update to a table is necessary; it is only necessary if values have changed, the one I was doing floatval on is one of about 6, the others are all text or integers.

This is all to facilitate the administrator making changes to the "master" spreadsheet & then re-uploading it. If nothing changed, no need to update.

But it goes further than that. Each customer has their own (for example) parts table; initially the same as the master, but over time the customer can add & change parts; when the master is updated, we want to let the customer decide if they want to accept the master changes (or not); they can accept them selective, one by one, or all.

So a separate table is kept with changes to master parts, the part #, date of change & type (add / change). In the customer record, we keep the last update date so we can generate a page (for each customer) that shows changes (if any) since the last update.
0
GaryCommented:
So maybe I should leave the floatval out & just round the raw text value?
Yep
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Dave BaldwinFixer of ProblemsCommented:
So maybe I should leave the floatval out & just round the raw text value?
I agree.  But don't count on it ever exactly matching a MySQL 'decimal' field.  PHP and MySQL do the arithmetic differently.  If you are using 'floats' in PHP, you need to read this whole page: http://us1.php.net/manual/en/language.types.float.php
0
Ray PaseurCommented:
Consider using number_format() to get the closest "exact" values for PHP float variables.  You will also want to search the PHP.net web site for "type juggling" and "comparisons" because PHP will not make dependably accurate comparisons of float numbers.

Sorry, that's just the way it works (or does not work) in PHP.
0
Richard KortsAuthor Commented:
OK, all, I will post another question that simply asks "how do I compare these values for equality."

Keep tuned.
0
Ray PaseurCommented:
Easy.  Save yourself the new question.  Use number_format() and compare the strings.
0
Dave BaldwinFixer of ProblemsCommented:
I guess you didn't read the PHP floats page where it describes the very limited methods for comparing float values in PHP.
0
Ray PaseurCommented:
@DaveBaldwin:
... didn't read the PHP floats page ...
You can lead a horse to water... Maybe the large red warning box scares people away from reading?  It's amazing to me that, with PHP more than 10 years old, we still get a half dozen questions about this every season!  It's almost as if people don't want to understand the language.  With the best online documentation anywhere they still find it's easier to copy some snippet of crapcode from the internet than it is to gain a little knowledge and apply it.
0
Richard KortsAuthor Commented:
Thanks Ray, that works perfect.
0
Dave BaldwinFixer of ProblemsCommented:
Yep, Ray, we see that almost everyday.  Is there a word for "fear of knowledge"?
0
Ray PaseurCommented:
maybe ignoraphilia?  I'll see if I can add that to the Urban Dictionary ;-)
0
Dave BaldwinFixer of ProblemsCommented:
ignoraphilia
Good word...
0
COBOLdinosaurCommented:
Saving that... I have some candiates in mind maybe I will start an "ignoraphilia Hall of Fame"

Cd&
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.