4

So I have done a fair bit of research on how to store "money" in a database and I think the system I want to use is

Converting Money into CENTS and then storing the CENTS in a MySQL DB with a field type of DECIMAL (19,4).

My question is, IF I have an input field from the user... how do I deal with multiple input types. IE:

$input = "1,346.54"
$input = "10,985.23"
$input = "110,400.50"
$input = "1033.44"

etc etc...

What would be the best method for converting this to CENTS? As we have to deal with 'strings' and convert them to INT, and divide by 100... Everything that I try throws issues because of the "comma" separation with the numbers.

Any thoughts or direction would be greatly appreciated.

AJ.
  • 27,586
  • 18
  • 84
  • 94
Justin
  • 2,502
  • 7
  • 42
  • 77
  • 1
    You should also note, that some countries/locales switch the meaning and position of the comma and the period in numbers: e.g. 10.985,23 – nfechner Jul 08 '11 at 19:13
  • 2
    Similar: http://stackoverflow.com/questions/5059831/parse-currency-from-string – AJ. Jul 08 '11 at 19:13
  • Hm, so your question is actually how to remove the "comma" from the input strings? – hakre Jul 08 '11 at 19:13
  • Out of curiosity, why use a `DECIMAL` column and then convert everything to cents so you don't have to worry about fractions? Just store the (potentially-fractional) dollar amount in the DECIMAL column and avoid this whole issue... – Dan J Jul 08 '11 at 19:32
  • @djacobson I am converting to cents purely for calculations down the road... it's easier to calculate against 'cents' as a whole versus dealing with 'rounding' and other issues... this way you don't get leaks' of money in obscure locations. – Justin Jul 08 '11 at 23:07
  • 1
    Generally you either convert to Cents and store as an INTEGER, OR leave as Dollars/Cents, and store as DECIMAL(13,4) assuming you want to remain compatible with GAAP. Your decision to move to Cents is the right one, but the decision to use Decimal is unnecessary and you lose some of the benefit of Integer storage and manipulation. – oucil May 29 '15 at 15:14

7 Answers7

9
function getMoneyAsCents($value)
{
    // strip out commas
    $value = preg_replace("/\,/i","",$value);
    // strip out all but numbers, dash, and dot
    $value = preg_replace("/([^0-9\.\-])/i","",$value);
    // make sure we are dealing with a proper number now, no +.4393 or 3...304 or 76.5895,94
    if (!is_numeric($value))
    {
        return 0.00;
    }
    // convert to a float explicitly
    $value = (float)$value;
    return round($value,2)*100;
}
Justin
  • 207
  • 1
  • 6
  • Added some more to strip out commas and other stuff. – Justin Jul 08 '11 at 19:22
  • 1
    What I don't like about this system is the use of Floats... as I hear about the un-accuracies when dealing with calculations... – Justin Jul 08 '11 at 20:57
  • I have made a rendition of this and made it work for now... it's not fool-proof unfortunately. But should work for now... – Justin Jul 08 '11 at 22:55
6

Looks like there is a NumberFormatter class which provides a parseCurrency method. Have a look at http://www.php.net/manual/en/numberformatter.parsecurrency.php

The example provided is

$fmt = new NumberFormatter( 'de_DE', NumberFormatter::CURRENCY );
$num = "1.234.567,89 $";
echo "We have ".$fmt->parseCurrency($num, $curr)." in $curr\n";
Rob Cowie
  • 22,259
  • 6
  • 62
  • 56
  • Seems like this is only for PHP 5.3... which I don't have on my server. – Justin Jul 08 '11 at 19:24
  • 2
    +1 PHP provides these nice classes for us, why reinvent the wheel with creating a custom parser? – Dalton Conley Jul 08 '11 at 19:24
  • @Justin; Unlucky :( If you can get hold of the source, you might still be able to use it by including it yourself. Depends if it relies on something 5-specific. – Rob Cowie Jul 08 '11 at 19:30
2

You can remove the commas like this:

$input = str_replace( ',', '', $input);

At this point, you can convert to cents by converting to a float and multiplying by 100. However, this is probably unnecessary. You would potential encounter precision issues when performing math operations, but simply storing the values in the database can be done in the original form without alteration of the value (assuming your DB tables are properly structured):

$input = (float)str_replace( ',', '', $input);
George Cummins
  • 28,485
  • 8
  • 71
  • 90
1
function convert_to_cents($v)
  {
    $v = str_replace(',','',$v);
    $p = explode('.',$v);
    if(strlen($p[1])<2){ $p[1] = $p[1]*10;}
    return ($p[0]*100)+$p[1];
  }
1

This converts most of the decimal currencies to their subunits.

$1,234,567.89 = 123456789

£ 1,234,567.89 = 123456789

€1.234.567,89 = 123456789

12,34 EUR = 1234

12,34 € = 1234

12,30 € = 1230

1,2 = 102

function convertDecimalCurrencyToSubUnit($str)
{
    if( preg_match('/^(.+)[^\d](\d|\d\d)[^\d]*$/', $str, $m) )
        return intval(preg_replace('/[^\d]/', '', $m[1]) . ( (strlen($m[2])>1) ? $m[2] : ('0' . $m[2]) ));
    return 0;
}
Tom
  • 190
  • 3
  • 15
0

Probably just remove the ',' and the '.' from the string, the result is the amount in cents.

Himadri Choudhury
  • 10,217
  • 6
  • 39
  • 47
-3

You will probably need to parse the string from the back using strrpos ... If you find a comma 2 spots from the end, then its prob safe to assume its foreign currency and those are the CENTS... Once you determine that, use a regex to strip the remaining commas (after you convert the "CENTS" comma to a decimal of course) ... Now you have a normal DEC number to play with.

Use this to find the last comma in your string ... strrpos

Use this to replace the commas preg_replace

Here is a helpful regex website .. regexlib

//Finding the last comma

$yourCommaPos = strrpos($myString, ',');
if ($yourCommaPos == strlen($myString) - 2) {
 //Comma is foreign currency decimal
 // Replace with '.'
} else {
 //Not foreign Currency so...
 //Strip Commas
 preg_replace(',', '', $myString);
}
Brian Patterson
  • 1,615
  • 2
  • 15
  • 31
  • Problems arise here is that what if they just input, $100... and not $100.00 then it error's out as well. – Justin Jul 08 '11 at 20:58