0

It appears as though the Subscriptions add-on stores the subscription amount as an integer in the database rather than a decimal. For example, a subscription amount of £120.00 is stored in the database as '12,000'.

This is causing a problem for, I think, any amount over 1000. For example, if I create a subscription with an amount of £1250, which gets stored in the database as '125,000', the customer actually only gets billed £1. Also, on the Subscriptions page within the module's settings, it displays the value of the subscription as £1.00.

Also, I don't think the payment gateway makes a difference but just in case, I'm using Stripe.

I'm not sure if this way of storing amounts in the database was deliberate, but it seems very odd.

Improve this question
4
  • Hey Pete, when you say the data is stored as 125,000 is that literal, i.e. is that comma in the database? Or is that just formatting when you typed the post??
    – Blatant
    Commented May 15, 2015 at 10:19
  • That's literal. The commas are stored in the database.
    – Pete H
    Commented May 15, 2015 at 10:58
  • maybe, just maybe this is the issue (parsing out the comma), perhaps you could replace this with a simple number for one subscription and re-test?
    – Blatant
    Commented May 15, 2015 at 13:14
  • Sorry, I was mistaken. The numbers are stored WITHOUT commas. It was my database client (HeidiSQL) displaying the commas. Sorry about that, I've only recently started using HeidiSQL and I'm still getting used to it!
    – Pete H
    Commented May 15, 2015 at 14:25

1 Answer 1

Reset to default
0

It's quite common for DB's to store monitary amounts as pence in integers. I beleive this is for DB optimisation as it's easier to format a number with pretty currency formatting than store the formatted number.

floats are another possability but take up more memory.

Also many systems like this are not region locked, in that they could be used with any currency in any country. By using a DB that is not necessarily locale aware it's probably better to stich with lowest denomination as an integer. makes it easy to store $231.45 dollars or £12.06 GBP or Y0.12 yen. no need for DB locale operations, PHP can parse the number into the correct denomination on display.

I realise we have currency and money typess in things like SQL Server, but in my experiance this causes problems (or at least conversions) specially when dealing with multi-currency sites.

For example, suppose we're living in the past and making a website to support 'old money' (pounds, shillings and pence) and 'new money' (just pounds and pence). For New money it's simple, £6 6p = (6 * 100) + 6 = 606, thats an integer. For old money the value is £2 10s and 6p, to represent this as an integer all we need to do is (2 * 240) + (10 * 12) + 6 = 480 + 120 + 6 = 606, there's our integer.

Now, that's a nice discussion point however (and here comes the disappointment) I can't answer the OP Question, but it certainly sounds like you've got a bug (In stripe??) there.

Improve this answer
1
  • I didn't know it was so common, thanks for the info. I don't think the bug is specific to Stripe, as the amount is displayed incorrectly on the Subscriptions page of the module settings. That's before any payment or communication with Stripe has been made.
    – Pete H
    Commented May 15, 2015 at 8:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.