Select float from MySQL with correct precision

Question

Common trouble that I've searched and read about and still stuck.

I have a table with trades, where trade price is declared as float. I cant' use decimal because different stocks have different precision in their price.

If a look at the table via phpMyAdmin, all numbers look correct - as they were inserted, as you can see on screenshot below, price (p) is 0.709 which is correct.

But when I select that trade from table using regular zend framework, I get price which is 0.70899999141693

How can I make a select which works same as MyAdmin select does? And receive number with correct precision? If myAdmin does the job, it can be possible in zend also, am I right?

EDIT: I can't use ROUND and I cant use DECIMAL because I don't know correct precision. e.g. price of Apple has 2 decimals in precision, but price of VTB Bank has 6 decimal in precision

EDIT2: Here are more examples on same select within myAdmin and zend.

Trade 8838397 was made by price 0.01156. I've inserted it in database and that's what I get in myAdmin:

If I select it with php, I get this:

I assume, myAdmin somehow guesses precision and does it really good. How???

Answer 1

Quote "I have a table with trades, where trade price is declared as float. I cant' use decimal because different stocks have different precision in their price."

I daresay this is nonsense. If you want precise data then use a precise type. You would only use float if the values exceeded the range DECIMAL offers, which is not the case here. DECIMAL(65,30) handles values with up to 30 digital places. That should be enough for all your values, I guess.

I suppose you are mixing internal storage with data display here. If the number of decimal places is an information needed, then store this information:

trade_id  office  price  precision
12345678  123456  0,709  4

A query:

select trade_id, office, round(price, precision) as price
from trades;

Result:

trade_id  office  price
12345678  123456  0,7090

EDIT: As to "MyAdmin somehow already solved it, I just can't undertand how":

It doesn't. I doubt that it will display 0.120 for an entered 0.120 and 0.12000 for an entered 0.12000. The dbms will store the same float value for both and phpMyAdmin has no way of telling they are to be treated differently. The only thing that phpMyAdmin probably does is to round to a maximum number of decimal places, say 6. select round( 0.70899999141693 , 6) results in 0.709.