Bitcoin Forum
April 23, 2024, 03:29:24 PM
 News: Latest Bitcoin Core release: 27.0 [Torrent]
 Home Help Search Login Register More
 Pages: [1]
 Author Topic: [SOLUTION] How to calculate and store Bitcoin balances with PHP and MySql?  (Read 527 times)
BisonPoint (OP)
Newbie

Offline

Activity: 19
Merit: 0

 May 09, 2017, 12:00:47 PM

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
1713886164
Hero Member

Offline

Posts: 1713886164

Ignore
 1713886164

1713886164
 Report to moderator
1713886164
Hero Member

Offline

Posts: 1713886164

Ignore
 1713886164

1713886164
 Report to moderator
The grue lurks in the darkest places of the earth. Its favorite diet is adventurers, but its insatiable appetite is tempered by its fear of light. No grue has ever been seen by the light of day, and few have survived its fearsome jaws to tell the tale.
Advertised sites are not endorsed by the Bitcoin Forum. They may be unsafe, untrustworthy, or illegal in your jurisdiction.
1713886164
Hero Member

Offline

Posts: 1713886164

Ignore
 1713886164

1713886164
 Report to moderator
1713886164
Hero Member

Offline

Posts: 1713886164

Ignore
 1713886164

1713886164
 Report to moderator
1713886164
Hero Member

Offline

Posts: 1713886164

Ignore
 1713886164

1713886164
 Report to moderator
Bitsky
Hero Member

Offline

Activity: 576
Merit: 514

 May 09, 2017, 08:12:34 PM

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?

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.

Bounty: Earn up to 68.7 BTC
Like my post? Feel free to drop a tip to 1BitskyZbfR4irjyXDaGAM2wYKQknwX36Y
BisonPoint (OP)
Newbie

Offline

Activity: 19
Merit: 0

 May 11, 2017, 01:29:29 PMLast edit: May 11, 2017, 03:08:51 PM by 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.

Quote
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
Hero Member

Offline

Activity: 576
Merit: 514

 May 11, 2017, 06:41:56 PM

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)?

Bounty: Earn up to 68.7 BTC
Like my post? Feel free to drop a tip to 1BitskyZbfR4irjyXDaGAM2wYKQknwX36Y
 Pages: [1]