Jump to content

Archived

This topic is now archived and is closed to further replies.

rcouser

Datatype for storing Money in MySQL

Recommended Posts

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.

Share this post


Link to post
Share on other sites

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 :)

Share this post


Link to post
Share on other sites

Hey Canman2005,

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

Share this post


Link to post
Share on other sites

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

?>

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

DECIMAL.

 

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.