[SOLUTION] How to calculate and store Bitcoin balances with PHP and MySql?

[BisonPoint]

About

This article will describe very easy and safe way to make simple Bitcoin balance calculations
and best approach to store them safely inside MySql (SQL) database, using PHP scripting language.

The Problem

Whenever you are building any Bitcoin web application, you will probably have to store some Bitcoin balances,
and make simple calculations to update them;
and, if you already tried to do this, you will know that using traditional Integers, Big-Integrers, and Floats can cause serious problems.

That is why most developers use Strings and Character arrays.
To implement this they usually choose one of two PHP libraries BC Math or GMP.
Although these libraries are great for doing complex operations, they are too heavy for doing simple tasks,
like calculating user balances, which use simple arithmetic.

Solution

To solve this, I wrote simple PHP Calculator class (https://github.com/BisonSoft/BigNumber_PHP)
which take two integers represented as strings (without decimal point), and do addition, subtraction, multiplication, division, and modulo operation.

In practice

1. To store Bitcoin balances, use strings and store them as Satoshis

5432 BTC store as "154320000"
0.0599 BTC store as "5990000"

2. In MySql define balance column as CHAR rather than VARCHAR, or TEXT, it will improve performance

CHAR(24) is large enough for any practical purposes
Set column DEFAULT option to '0'

3. PHP Calculator class

It accepts strings containing digits only: "12345", "0012345" etc.
Invalid strings will return Error: "-12345", "1.2345" etc.
Dividing with zero will return Error also: "1234" / "0" = "E"
Class takes two stings and returns solution number as string: "1234" + "66" = "1300"
When dividing two numbers, class returns number with decimal point: "87" / "12" = "7.25"

4. Security

Beside balance column it is wise to set a hash column and to verify balance each time record is accessed.
In this way any corruption to database can be verified.
Sha1 algorithm is sufficient for this

Example:
Balance: "112345500"
Sha1: 1bcd3635438e41be2b0329938f0a55c35f7b6be5

[Bitsky]

Whenever you are building any Bitcoin web application, you will probably have to store some Bitcoin balances,
and make simple calculations to update them;
and, if you already tried to do this, you will know that using traditional Integers, Big-Integrers, and Floats can cause serious problems.

That is why most developers use Strings and Character arrays.

No. Just no.
I've been using decimal(16,8) for years without a single issue; and in contrast to your suggestion, it lets me do standard arithmetic operations in SQL. Same for bigint.
Care to give examples for these "serious problems"? Why don't you file a bug to get those problems fixed?

Let's check what MySQL says about this:

Code:

 The declaration syntax for a DECIMAL column is DECIMAL(M,D). The ranges of values for the arguments are as follows:

    M is the maximum number of digits (the precision). It has a range of 1 to 65.

    D is the number of digits to the right of the decimal point (the scale). It has a range of 0 to 30 and must be no larger than M. 

The maximum value of 65 for M means that calculations on DECIMAL values are accurate up to 65 digits. This limit of 65 digits of precision also applies to exact-value numeric literals, so the maximum range of such literals differs from before. 

[BisonPoint]

Bitsky, I absolutely agree with you,
for simple and practical purposes DECIMAL and BIGINT are more than enough for data storage and SQL operations.

However,
PHP language has some serious limitations when performing mathematical operations with big numbers, and that is why I built this class.
There are two PHP libraries for this purpose (BC Math and GMP), but I wanted simple lightweight class, nothing complicated.

Second thing I wanted, is to have the ability to store numbers of any length (in practical sense), without the need to modify
data type of particular row, that is why I choose CHAR and VARCHAR over DECIMAL and BIGINT.

Care to give examples for these "serious problems"? Why don't you file a bug to get those problems fixed?

The problem that I had is related to inability of PHP language to deal with large floating-point numbers; after some number of decimals it just rounds them
(PHP manual better describes the problem that I could do in this reply http://php.net/manual/en/language.types.float.php).

So, I apologize if I sounded misleading in my original post, my intention was to provide the solution for some unusual circumstances.

[Bitsky]

You could still store the values as DECIMAL or BIGINT and benefit from sql's operations, like SUM(). At worst, you can CAST() your SELECT to CHAR when using a query in PHP.
PHP has always been more like a bandaid language (like, is it ($haystack, $needle) or ($needle, $haystack) for whatever function)?