Php and money calculations best practice

Hi all. In a table I have set the 'price' field as decimal(6,2). Now I'm going to write the code to manage prices and I read that the best practice is to treat all currency values as integer, dividing the currency value per 100, then do calculation and finally formatting the result in a readable format.

Is this correct?
And can I get from the db a decimal value covert it in integer do calcs and then format the result or it is better to change the field type and store values as integers?

I wish to know the best professional practice.
Thank you all in advance
LVL 31
Marco GasiFreelancerAsked:
Who is Participating?
 
Dave BaldwinFixer of ProblemsCommented:
No, I'm not.  I'm saying that the data in queries from PHP to MySQL and back is always in text format, even if the column is a number type in the table.  Yes, there are a couple of binary formats but nothing that applies here.

In order to force PHP to view variable data as a number, I often multiply it by 1 or add 0 to it.  When I need to like in adding tax to an order, I do that, add the numbers, and use 'sprintf' to reformat the result for the display and/or database.
0
 
Dave BaldwinFixer of ProblemsCommented:
decimal(6,2) is 9999.99 maximum.  I know the 'old' method used to be to use 'int's for money.  But I don't do that.  The data sent to and retrieved from the database is text.  If I need a number formatted as currency, I use 'sprintf' to do it.  Like this:
sprintf("%.2f", $price)

Open in new window

http://php.net/manual/en/function.sprintf.php
0
 
Marco GasiFreelancerAuthor Commented:
Hi, Dave, thanks for your reply. Let me understand: are you saying I have to set all fields as text/varchar?
And formatting problems apart (I know sprintf), calculate a sum of several values or the tax percentage must be done with text values? I'm confusing...
0
 
Marco GasiFreelancerAuthor Commented:
Thank you Dave
0
 
Dave BaldwinFixer of ProblemsCommented:
You're welcome!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.