I am trying to create an ad platform for a bitcoin faucet and hence using MySQL as DB. There is a reason I mention bitcoin in my question, because I believe my problem is somewhat associated with it. Bitcoin takes into account decimal places up to eighth decimal. So, the payment coming from someone can be 0.01234567 BTC, which generally does not happen for normal monetary transaction. Now, in my SQL statement, I am trying to check whether an advertiser has paid the exact amount for his ad space and thereby update the order to be successful. Following is the statement I am using...
UPDATE order_table
SET status = 'success'
WHERE orderid = $orderid
AND amount <= $amount
The amount field has been defined as DOUBLE. Now, when the amount value is 0.01 BTC, the order should not be successful when $amount is lower than that. But, when an user is paying 0.0099 BTC, it is getting updated as successful !!! I have read the following solution, but it is dealing with FLOAT...
MySQL "greater than" condition sometimes returns row with equal value
I wonder if the same logic is applicable for DOUBLE as well and can I blindly convert the DOUBLE data type to DECIMAL to get rid of the problem ?
Edit: Thanks for the Best data type to store money values in MySQL link in the comment. I wonder, if I can employ the Satoshi denomination of bitcoin to solve the problem. In this case, the query will be as follows...
UPDATE order_table
SET status = 'success'
WHERE orderid = $orderid
AND amount*100000000 <= $amount_in_satoshi
