Menu
I am trying to do a SELECT match on a table based upon an identifier and a price, such as:
SELECT * FROM `table` WHERE `ident`='ident23' AND `price`='101.31'; The above returns zero rows, while if you remove the price='101.31' bit it returns the correct row.
Doing a...
SELECT * FROM `table`; Returns the same row as above and quite clearly states that price='101.31'. Yet select fails to match it. Changing = to <= makes it work - but this is not exactly a solution.
Is there a way of casting the MySQL float to 2 digits before the operation is performed on it, thus making the above SELECT work (or some other solution)?
Thanks!
424
MySQL permits a nonstandard syntax: FLOAT( M , D ) or REAL( M , D ) or DOUBLE PRECISION( M , D ) . Here, ( M , D ) means than values can be stored with up to M digits in total, of which D digits may be after the decimal point. For example, a column defined as FLOAT(7,4) is displayed as -999.9999 .
%d – the argument is treated as an integer, and presented as a (signed) decimal number. %s – the argument is treated as and presented as a string. in your examples, $slug is a string and $this->id is an integer.
Casting to a decimal worked for me:
SELECT * FROM table WHERE CAST(price AS DECIMAL) = CAST(101.31 AS DECIMAL); However, you may want to consider just making the price column a DECIMAL in the first place. DECIMAL is generally considered to be the best type to use when dealing with monetary values.
146
It doesn't work because a float is inherently imprecise. The actual value is probably something like '101.3100000000001' You could use ROUND() on it first to round it to 2 places, or better yet use a DECIMAL type instead of a float.
30
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
