By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
445,885 Members | 1,474 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 445,885 IT Pros & Developers. It's quick & easy.

problem querying a float column

P: n/a
I have a column in a MySQL database called price and of type FLOAT(8,2).

I then insert two rows into my table, one where price = 100.00 and one
where price = 100.01, and then try to find these rows:

query 1:
SELECT price
FROM table
WHERE price = 100.00

query 2:
SELECT price
FROM table
WHERE price = 100.01

query 1 successfully returns the row in question, while query 2 returns
the empty set. Using quotes around the search term does not affect it
at all (as it shouldn't since I "should" be searching a float...)

If I try to insert and then search for any number that does not end in
00, I get the empty set. I tried using LIKE and was able to find the
row, but there must be another way to test actual equality.

Thanks very much in advance.
Jul 17 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
Marcus wrote:
I have a column in a MySQL database called price and of type FLOAT(8,2).


It's not a good idea to use FLOAT for numbers which are meant to be
exact. A better data type for prices would be DECIMAL.

JP

--
Sorry, <de*****@cauce.org> is a spam trap.
Real e-mail address unavailable. 5000+ spams per month.
Jul 17 '05 #2

P: n/a
NC
Marcus wrote:

I have a column in a MySQL database called price and
of type FLOAT(8,2).
Bad idea. Money variables should be long integers and
stated in cents (pence, centime, etc.)
there must be another way to test actual equality.


With floats, equality is a difficult concept. Computers
do not compute floating-point numbers with absolute
precision. What you think is 100.00 may in fact be
100.00000000000000002 or 100.00000000000000004, which,
needless to say, will be treated as different numbers.

Cheers,
NC

Jul 17 '05 #3

P: n/a
Marcus wrote:
I have a column in a MySQL database called price and of type

FLOAT(8,2).
<snip>

http://dev.mysql.com/doc/mysql/en/pr...ith-float.html

--
<?php echo 'Just another PHP saint'; ?>
Email: rrjanbiah-at-Y!com Blog: http://rajeshanbiah.blogspot.com/

Jul 17 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.