473,322 Members | 1,911 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

How do I get PHP to respect my MYSQL double() data type?

My mysql data type is double(16,2) with '0.00' specified as default.
These represent dollars and cent amounts.

My question is: How do I perform simple mathematical operations on
these money amounts without PHP changing them to integers? I only need
to add or subtract these numbers without any rounding or chopping off
of the '.00' if they come up zero cents.

Looking at the PHP docs, the only helpful suggestion I could find is
that if you keep track of money, you should add penny amounts and
perform all your math operations as integers. Why can't PHP handle
simple math on floats? I've tried converting my data types to float,
I've tried the money_format() function which is no good to me because
I'm on Windows, and I've tried the number_format() which does not do
what I need.

What do I need?

I need to have (0.00 + 0.00) return 0.00 instead of '0', or (937.00 +
2.10) return 939.10 instead of 939.1

Or, has someone written a GPL set of php functions or a calculator
class to deal with money amounts?

Thanks-

JM
J Moore
Jun 27 '08 #1
6 3849
John Moore wrote:
My mysql data type is double(16,2) with '0.00' specified as default.
These represent dollars and cent amounts.

My question is: How do I perform simple mathematical operations on
these money amounts without PHP changing them to integers? I only need
to add or subtract these numbers without any rounding or chopping off
of the '.00' if they come up zero cents.

Looking at the PHP docs, the only helpful suggestion I could find is
that if you keep track of money, you should add penny amounts and
perform all your math operations as integers. Why can't PHP handle
simple math on floats? I've tried converting my data types to float,
I've tried the money_format() function which is no good to me because
I'm on Windows, and I've tried the number_format() which does not do
what I need.

What do I need?

I need to have (0.00 + 0.00) return 0.00 instead of '0', or (937.00 +
2.10) return 939.10 instead of 939.1

Or, has someone written a GPL set of php functions or a calculator
class to deal with money amounts?

Thanks-

JM
J Moore
You can do it as floats - PHP should automatically handle it for you.
In your case, the value is being returned correctly. Just the default
display will drop trailing zeros. Use printf() with the appropriate
formatting string to get what you want, i.e. printf("%01.2f", $value);

But the problem with floats is not just PHP - it's common to every
language on the PC, no matter what. It has to do with the way the items
are stored. The IEEE format saves data as base 2. An amount such as
$0.10 cannot be specified exactly - it's a repeating decimal, similar to
1/3 is in decimal.

As a result, you don't have exactly 0.10. You have something like
0.099999999999987. Close, but not exact. And it can affect calculations.

Mainframes typically have a decimal type as a native value - where data
is stored exactly. There are emulators in some languages such as C++,
but they are much slower than working in the native numeric formats.

So the recommendation is to store as cents - and only insert the decimal
place when you're ready to display the value. That way you get exact
answers.
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================

Jun 27 '08 #2
..oO(John Moore)
>My mysql data type is double(16,2) with '0.00' specified as default.
These represent dollars and cent amounts.
Floating point numbers are not reliable enough for storing prices.
The decimal type would be better.
>My question is: How do I perform simple mathematical operations on
these money amounts without PHP changing them to integers? I only need
to add or subtract these numbers without any rounding or chopping off
of the '.00' if they come up zero cents.

Looking at the PHP docs, the only helpful suggestion I could find is
that if you keep track of money, you should add penny amounts and
perform all your math operations as integers. Why can't PHP handle
simple math on floats?
This has nothing to do with PHP, but simply with the fact that most
floating point numbers can't be exactly represented in binary form.
The precision of floats is limited by the hardware.

An example from the PHP manual:

floor((0.1+0.7)*10)

The expected result would be 8, but usually you'll get 7.
>I've tried converting my data types to float,
I've tried the money_format() function which is no good to me because
I'm on Windows, and I've tried the number_format() which does not do
what I need.

What do I need?

I need to have (0.00 + 0.00) return 0.00 instead of '0', or (937.00 +
2.10) return 939.10 instead of 939.1
Use fixed-point (integer) arithmetics. Calculate with cents and only
format the values for the output. For the output you can use printf().

Micha
Jun 27 '08 #3
John Moore wrote:
My mysql data type is double(16,2) with '0.00' specified as default.
These represent dollars and cent amounts.

My question is: How do I perform simple mathematical operations on
these money amounts without PHP changing them to integers? I only need
to add or subtract these numbers without any rounding or chopping off
of the '.00' if they come up zero cents.

Looking at the PHP docs, the only helpful suggestion I could find is
that if you keep track of money, you should add penny amounts and
perform all your math operations as integers. Why can't PHP handle
simple math on floats? I've tried converting my data types to float,
I've tried the money_format() function which is no good to me because
I'm on Windows, and I've tried the number_format() which does not do
what I need.

What do I need?

I need to have (0.00 + 0.00) return 0.00 instead of '0', or (937.00 +
2.10) return 939.10 instead of 939.1

Or, has someone written a GPL set of php functions or a calculator
class to deal with money amounts?

Thanks-

JM
J Moore
Hi John,
The function you want to use is money_format (
http://au2.php.net/manual/en/function.money-format.php ).
This example takes any number and puts 2 decimal places after the number
even if it doesn't have any.
<?php
$number = 100.1;
echo(money_format("%.2n", $number));

?>

The part of interest is the format string "%.2n" which follows the
required format in the manual.
# % required % symbol
# Optional right precision (In this case .2)
# a required conversion character (In this case n)

--
Hope this helps, Cheers
Leigh Finch
www.phpmaniac.net
Jun 27 '08 #4
On Thu, 12 Jun 2008 17:06:54 -0400, in comp.lang.php Jerry Stuckle
<js*******@attglobal.netwrote:

>>
I need to have (0.00 + 0.00) return 0.00 instead of '0', or (937.00 +
2.10) return 939.10 instead of 939.1
>
You can do it as floats - PHP should automatically handle it for you.
In your case, the value is being returned correctly. Just the default
display will drop trailing zeros. Use printf() with the appropriate
formatting string to get what you want, i.e. printf("%01.2f", $value);
Thanks, but printf doesn't return anything and I'd have to rewrite my
whole script to keep printf from spitting out my calculation before I
wrap it in html. Although it probably needs to be rewritten anyway.

Thanks for explaining how machines handle floats- I was blaming it on
PHP, almost in tears just to get a simple penny amount to show up on a
web page. For a quick fix I went back to number_format() and got that
working the way I wanted it to.

$dollar_amount = number_format($dollar_amount, 2, '.', '');

Seems to be working.

Thanks again,
J Moore
Jun 27 '08 #5
On Thu, 12 Jun 2008 23:01:12 +0200, in comp.lang.php Michael Fesser
<ne*****@gmx.dewrote:

>
Use fixed-point (integer) arithmetics. Calculate with cents and only
format the values for the output. For the output you can use printf().

Micha
Thank you, I will look into your suggestions. I notice OS Commerce
uses a decimal data type to store dollar amounts, so I'll change my
data types ASAP.

Thanks again,
J Moore
Jun 27 '08 #6
John Moore wrote:
On Thu, 12 Jun 2008 17:06:54 -0400, in comp.lang.php Jerry Stuckle
<js*******@attglobal.netwrote:

>>I need to have (0.00 + 0.00) return 0.00 instead of '0', or (937.00 +
2.10) return 939.10 instead of 939.1
>You can do it as floats - PHP should automatically handle it for you.
In your case, the value is being returned correctly. Just the default
display will drop trailing zeros. Use printf() with the appropriate
formatting string to get what you want, i.e. printf("%01.2f", $value);

Thanks, but printf doesn't return anything and I'd have to rewrite my
whole script to keep printf from spitting out my calculation before I
wrap it in html. Although it probably needs to be rewritten anyway.
Then use sprintf().
Thanks for explaining how machines handle floats- I was blaming it on
PHP, almost in tears just to get a simple penny amount to show up on a
web page. For a quick fix I went back to number_format() and got that
working the way I wanted it to.

$dollar_amount = number_format($dollar_amount, 2, '.', '');

Seems to be working.

Thanks again,
J Moore

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================

Jun 27 '08 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

21
by: Batista, Facundo | last post by:
Here I send it. Suggestions and all kinds of recomendations are more than welcomed. If it all goes ok, it'll be a PEP when I finish writing/modifying the code. Thank you. .. Facundo
6
by: David Hoffer | last post by:
Does C# have the same problem as C++ with code like this? if (GetDouble1() == GetDouble2()) { } In C++, due to the IEEE storage format, it is possible to have 2 doubles that are essentially...
3
by: android | last post by:
I require a function that takes a double as a parameter and returns the number of decimal places. What is the most efficient way in c# to find out the number of decimal places I have written a...
0
by: Roy | last post by:
Hi all, There is a column (col1) in Access data base that is adouble data type. In vb.net when I try to set a row filter for a view based on its table as: Dim dv As New DataView(MyDataTable)...
2
by: safren | last post by:
1. In c++ (or any other language) I'm copying bit by bit to a double variable dob_var. 2. If I copy dob_var to dob_var2 which is also a double I do not lose information. 3. This implies that the...
1
by: Ian Robinson | last post by:
Hi, I'm converting a desktop application Delphi/Paradox to a web server application PHP/MySQL What data type should I use in MySQL for the following paradox datatypes ? Boolean ? Currency ?
1
by: devathi | last post by:
How to make double number 1.961831656E9 to appear in 1961831656 format
1
by: vbwire | last post by:
i use vb 6.0 this is my work.. 1. Option Explicit 2. 3.
0
by: markwalsen | last post by:
I've been quite pleased with linking via MySQL Connector / ODBC 3.51 from Access to a remote MySQL database. However, I haven't figured out how to establish the link between a Data Type =...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.