how to store pricing (anyone have any helpers for price data?)

[eluser]tomdelonge[/eluser]
So I've never really done anything with pricing before. Say I'm adding a product to my online store. Should I add a $4.49 item as 449? And whenever I retrieve products use some sort of helper to put it back in the first form?

What other functions have you guys found helpful to do things with price data?

Any advice?

Thanks.

[eluser]Mischievous[/eluser]
simply put it in the database as 4.49 set the field as a decimal with value 7,2

[eluser]Ben Edmunds[/eluser]
Decimal or float works well with price data.

[eluser]Jondolar[/eluser]
You typically want to use decimal with currency fields because you can/will lose precision in a float field. Plus, floats make it harder to do simple comparisons after doing calculations since you might end up with 5.49999999 instead of 5.5.

Here is a nice reference I found:
http://www.hgarland.com/integers_vs_floating_points

[eluser]stommert[/eluser]
make an integer field and store everything in cents. use cents in your application and only convert it in to whatever you want in your view. It will save you a lot of headache.

[eluser]Aken[/eluser]
I disagree with stommert. A float has always worked fine for me when dealing with e-commerce (which is every day). A decimal may eliminate some extended remainder issues, but you should always sanitize your entries with PHP before applying them anywhere regardless of format. The PHP function number_format() works great!

[eluser]n0xie[/eluser]

A float has always worked fine for me when dealing with e-commerce (which is every day)

You DO know what a float is right?

Let me illustrate:

Code:

/* PHP */
<?php echo floor((0.1+0.7)*10); ?>

/* MySQL */
CREATE TABLE  `floatt` (
  `f` float DEFAULT NULL,
  `d` decimal(7,2) DEFAULT NULL
);

INSERT INTO `floatt` VALUES (0.33,0.33);

SELECT * FROM `floatt` WHERE f = 0.33;
0 rows returned

SELECT * FROM `floatt` WHERE f > 0.33;
1 row returned

SELECT * FROM `floatt` WHERE d = 0.33;
1 row returned

SELECT * FROM `floatt` WHERE d > 0.33;
0 rows returned

[eluser]Aken[/eluser]
Actually I was rather unfamiliar with the float column type in databases. I assumed it was similar to a PHP float. The clarification is appreciated.

I never did say that a float is the best resource to use, though. I said it works fine for me. And suggested that people always sanitize their numbers relating to money BEFORE entering them into a database, and not using it to round off numbers and such.

And I did not create the DB structure at my place of employment, and am not in a position to overhaul it to fix its stupidity. Which is a shame.

[eluser]n0xie[/eluser]
[quote author="Aken" date="1258444330"]Actually I was rather unfamiliar with the float column type in databases. I assumed it was similar to a PHP float. [/quote]
As my PHP example illustrates, the PHP float is subject to the same errors in calculation. This is inherent to the float data type.

One would expect that floor((0.1+0.7)*10) would be 8, not 7. This could be a major issue if you run any form of E-Commerce.

I never did say that a float is the best resource to use, though. I said it works fine for me.

I wonder about that. Maybe you haven't noticed it because you always assumed that float calculations wouldn't influence the outcome.

And suggested that people always sanitize their numbers relating to money BEFORE entering them into a database, and not using it to round off numbers and such.

Sanitation doesn't help here. It is inherent to the way computers do floating point calculations. See my PHP example.

[eluser]CI_avatar[/eluser]
just save it as it is. and make that the database field where you are going to store your data have a datatype of decimal.