How to store & process currencies in mysql / zend framework

Question

We want to store product prices and weight (kg/pound) in MySQL. Can somebody tell me what's the best way to do this?

double/decimal/... ?

We need to be able to display both USD and EURos. I don't know if it helps, but we use the Zend framework to build our application.

score 1 · Answer 1 · answered Mar 24 '10 at 21:34

for currency use decimal

from http://dev.mysql.com/doc/refman/5.1/en/numeric-types.html : 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.

score 1 · Accepted Answer · edited Jun 20 '20 at 09:12

1

Have you looked at the Zend_Currency family of functions?

This component works with all available locales and therefore knows about more than 100 different localized currencies. This includes informations like currency names, abbreviations, money signs and much more.

Zend_Currency has the advantage that already defined currency representations can be reused. You could also have 2 different representations for the same currency.

Zend_Currency allows you also to calculate with currency values. Therefore, it provides you an interface to exchange services.

If you like that part of the Zend Framework, I guess a lot of decisions will "sort themselves out" based on what they use to work with the values.

edited Jun 20 '20 at 09:12

Community

1
1

answered Mar 24 '10 at 21:35

Pekka

442,112
142
972
1,088

thanks, I'm using the zend_currency component right now. Can't figure out how to format currency like this though: $ 200,00 USD (first the symbol, then the amount, followed by the short name). Any code examples you might have? – Jorre Mar 25 '10 at 12:44
@Jorre I haven't worked with it myself yet (just getting into the ZF myself right now) but from what I can read here: http://framework.zend.com/manual/en/zend.currency.options.html it should definitely be possible to build custom formats. A cursory search doesn't reveal any examples, though. – Pekka Mar 25 '10 at 12:48
@Jorre here's an example how to set the format in general, maybe it helps in conjunction with the manual entries: http://stackoverflow.com/questions/2158698/zend-currency-symbol-is-displaying-1 – Pekka Mar 25 '10 at 12:49

score 1 · Answer 3 · answered Mar 24 '10 at 21:38

1

We always use decimal and add a currency_id field to denote currency.

You can create a currency table with id, and name, and sign and join it on queries for price.

answered Mar 24 '10 at 21:38

jps

11,347
3
23
19

thanks a lot, what would you use for weight? Kg/pounds? Decimal as well? – Jorre Mar 25 '10 at 12:44

How to store & process currencies in mysql / zend framework

3 Answers3