Datatype for storing Money in MySQL

[rcouser]

I know this is a PHP forum but hope someone can help with storing money in MySQL.

I have been following a book to create a simple e-commerce and it uses float(10,2) but when I type in 565.50 it is stored at 565.00.

I have search page after page on google and seems to a big debate.

Hopefully someone has the answer.

Regards.

[Canman2005]

Personally I don't see any major issues with just using VARCHAR but then im sure many would disagree, at least you know it can store your number as it's entered.

 

Shoot me if im wrong

[rcouser]

Hey Canman2005,

Can you calculate totals if the amount is VARCHAR. Say in a shopping cart.

[Fruct0se]

Try using Decimal

[MasterACE14]

use float.

 

then you can make a function like...

<?php
function money($value) {
return "$".number_format($value);
}

$money = 2000.00;

echo money($money);

// returns $2,000.00

?>

[rcouser]

Hey MasterACE14

The problem isn't the formatting of the value. It is that mysql cuts of the 50 from 565.50 making it 565.00. I might have to try decimal.

[MasterACE14]

then use Float. Float is the decimal type

[Canman2005]

Hey Canman2005,

Can you calculate totals if the amount is VARCHAR. Say in a shopping cart.

 

I believe so, I have never had any issues with using it, mostly because I have had to take existing large excel databases and don't want to chance messing up any money values, but then im not much of an expert and im sure other suggestions would be better, but personally I would stick with VARCHAR

[Barand]

DECIMAL.

 

You get the fixed decimal places without the precision errors inherent in float type. So it holds 123.45 instead of 123.4499999999999999

[TheStalker]

decimal(10,2)

[Mchl]

Why you should not use float for money:

 

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