Question about Bitcoin number precision for PHP/MySQL Web App

[NonProphet]

Hey I'm developing a php/mysql webapp that uses bitcoin and I've got a few questions.

The VPS is setup, bitcoind is running, and I have all the API calls where they need to be in the script, but I'm not sure how I should be handling the bitcoin amounts.

My main questions are: How should bitcoin values be stored in a database table?  Does any particular data type (int, double, etc) work best?
I saw the wiki page for proper money handling and there was a PHP function there called JSONtoAmount.  Can anyone clear up when that should be used?

Any help on this would be appreciated.  It's the last thing that needs to be done before we are up and running.

[1714016699]

1714016699

[1714016699]

1714016699

[1714016699]

1714016699

[1714016699]

1714016699

[1714016699]

1714016699

[1714016699]

1714016699

[ANSYSiC]

yesterday i read that the client handles BTC in multiples of the satoshi = int.

3,50000000 BTC = 35000000 satoshi

[NonProphet]

Sorry, I'm not following you on that.

[SimplePanda]

Sorry, I'm not following you on that.

https://en.bitcoin.it/wiki/PHP_developer_intro
Bitcoin amounts can range from 1 (0.00000001 BTC).

MySQL:

mysql> create table bitcoinTest (bitcoin double not null);
mysql> insert into bitcoin values (0.12345678);
mysql> select * from bitcoinTest;
+------------+
| bitcoin    |
+------------+
| 0.12345678 |
+------------+

So double works. On the other hand:

mysql> create table bitcoinTest (bitcoin float not null);
mysql> insert into bitcoinTest values (0.12345678);
mysql> select * from bitcoinTest;
+----------+
| bitcoin  |
+----------+
| 0.123457 |
+----------+

FLOAT lakes sufficient precision.

[NonProphet]

So if I use a double, then I shouldn't need to worry about the formatting stuff, because it already has enough precision?

[phorensic]

I use varchar(10) in my sql database for account balances.  Not sure what advantage or disadvantage it has.

[SimplePanda]

Can you be more specific about how you're moving values around?
Is this all within a PHP app talking to MySQL or are you interacting with bitcoind via JSON?

To the above - varchar probably isn't the best way to store BTC values. You're storing the value as a text string that represents the value rather than storing the value itself.

[NonProphet]

Users will be buying/selling so their account balances will be changing.  Just wanted to make sure that kind of basic math won't effect any rounding precision.

[gazd3k]

You are fine with double, unless you try doing some math with extreme values like adding 2000000BTC and 0.00000001BTC a few times, then you can hit some rounding errors.

[NonProphet]

Sounds good thanks for all the help everyone.

[ribuck]

You are fine with double, unless you try doing some math with extreme values like adding 2000000BTC and 0.00000001BTC a few times, then you can hit some rounding errors.

Well exactly. Like you say, you can hit some rounding errors. And you don't want rounding errors, because they are errors. So you are not "fine with a double". Financial math needs to be exact.

I use varchar(10) in my sql database for account balances

If you're using varchar, it's not sufficient to use varchar(10), which is not even enough to store a value of 12.12345678 bitcoins. Storage is cheap enough that you should use varchar(17), which can hold any number of bitcoins up to the maximum possible of 21000000.00000000.

If you're using numeric types, DECIMAL(16,8) will work perfectly.

You also need to use decimal-safe arithmetic in your PHP code. The "bcmath" and "gmp" libraries are decimal-safe math libraries for PHP.

Here's a reference to numeric types in MySQL:
http://dev.mysql.com/doc/refman/5.0/en/numeric-types.html

From that page:

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

[NonProphet]

Thanks for the help, your post was exactly what I was looking for.