SQL - best type to save money value?

[oliverj777]

Hello,

 

As you may know, money is displayed like so: 10.50 (from where I am from its £10.50, but anyway ...)

 

So what would you recommend as a type for SQL to save that value, I would prefer it to be numeric, but I would also like to save the decimal so when it comes to PHP, I can see what is pounds (dollars) and pence (cents) in which I will soon later add to a mathematical array.

 

Thanks, Ollie!

[shlumph]

Anything with decimals, use DOUBLE as a datatype and you should be good to go.

[oliverj777]

I get an error:

 

SQL query:

 

ALTER TABLE  `users` CHANGE  `due_balance`  `due_balance` DOUBLE( 20 ) NOT NULL

 

--- Do i need to do something special with the Length/Values? ---

[PFMaBiSmAd]

The DOUBLE data type requires a conversion into/out-of binary which introduces errors because some fractional values cannot be represented exactly in binary.

 

You should use the DECIMAL data type, which are stored as BCD values, exactly as entered -

 

The DECIMAL and NUMERIC data types are used to store exact numeric data values. In MySQL, NUMERIC is implemented as DECIMAL. These types are used to store values for which it is important to preserve exact precision, for example with monetary data.

[oliverj777]

sorted - I didn't know I had to change the length/value to a format of:  decimal(#,#)