Money values in MySQL

[Porte]

Hello,

What's the best method to store money values in a mysql?
Float, double..or what? varchar doesn't work because I'll need to sort values by order some times. How can you store the value as the following format: x,xxx.xx not just xxx.xx

Thank you,
-Ahmed

 

[Icespadez]

For money values I use a float. When the result is returned from a query you can format as you need.

 

[Porte]

Ah, so its possible to convert x,xxx.00 to xxxx.00 and xxxx.00 to x,xxx.00? What function can do this? I'm using php.

 

[axilant]

http://php.net/number_format

thats the function your looking for i think

 

[Porte]

Well, this function does it..but I don't want to change the values since I'm dealing with money, like they convert 1234.56; to 1,235. I would rather want it to be 1,234.56 just the same but with formatting of thousands.

Edit: I figured something It worked for me, but I'm not sure if its 100% accurate

<?
$number = 1000;
$do_format = number_format($number, 2, '.', ',');
echo $do_format;
?>

Output: 1,000.00

It seems to be working fine so if your values are stored in mysql as float type numbers just extract the value and format it using that function, it should look fine with thousands, this is useful for online stores/IPN.
thanks for everyone assistance.

Now, that we created the number format; we can still convert it to a regular float to use it in database or something;

echo str_replace(',', '', $do_format);

like this, I tried it and it worked.

Thanks axilant for the key, that function was what I was looking for.
Good luck everyone