Hi everybody,
I am facing problem querying MySQL tables with PHP. Actually I have a field in a table with float data type. In one record i've value 2.95 in the float field. When I retrieve and print it from PHP code, it prints 2.9500000476837. I know it's 'coz data type is float and there are few precision issue involved. But when i run the following query from PHP ==>
"SELECT * FROM url_keywords WHERE bid = 2.95"
or
"SELECT * FROM url_keywords WHERE bid = 2.9500000476837"
I get nothing in both case. Please help me. Waiting for ur response.
Regards
Ashish
problem in retrieving float from MySQL
Moderator: General Moderators
- twigletmac
- Her Royal Site Adminness
- Posts: 5371
- Joined: Tue Apr 23, 2002 2:21 am
- Location: Essex, UK
You could you the LIKE keyword to do a wildcard match. I'm sure someone who stores more mathematical type stuff in databases may have a better solution but try this:
Mac
Code: Select all
SELECT field1, field2, field3 FROM table WHERE field LIKE '2.95%'Thanks Mac
Hi Mac,
Your solution works but there are chances of minor mistakes as there is a possiblity that 2.94 and 2.95 be considered same by MySQL. I have worked out the solution. I hope this helps u in near future or others.
So i guess the solution for this is
$query = "select * from url_keywords where abs(bid - 2.95) < .001" ;
or
u can do the following thing==>
$bid = 2.95;
$bid1 = 2.95 - 0.001; // depending on ur precision requirement
$bid2 = 2.95 + 0.001; // depending on ur precision requirement
$query = "select * from url_keywords where bid between $bid1 and $bid2" ;
Both the solutions(at least for me) seems to work perfectly. Anyway thanks alot for ur help and effort.
Regards
Ashish
Your solution works but there are chances of minor mistakes as there is a possiblity that 2.94 and 2.95 be considered same by MySQL. I have worked out the solution. I hope this helps u in near future or others.
So i guess the solution for this is
$query = "select * from url_keywords where abs(bid - 2.95) < .001" ;
or
u can do the following thing==>
$bid = 2.95;
$bid1 = 2.95 - 0.001; // depending on ur precision requirement
$bid2 = 2.95 + 0.001; // depending on ur precision requirement
$query = "select * from url_keywords where bid between $bid1 and $bid2" ;
Both the solutions(at least for me) seems to work perfectly. Anyway thanks alot for ur help and effort.
Regards
Ashish