Deleting a row based on a float column in Mysql/PHP?

Question

This

DELETE FROM `z_ratings` 
WHERE `score` = '5.4' 
  AND `media_id` = '242' 
  AND `user_id` = '1'

will not delete the following row:

Can anybody explain why?

(I'm assuming the issue is with the float since both other columns are integers)

@Abdulla This is the query generated by active records in Codeigniter though. I'll check if there is a way to remove those single quotes — Callombert, Jan 15 '16 at 16:25
Can you switch type to decimal? http://stackoverflow.com/questions/2567434 — fafl, Jan 15 '16 at 16:26

score 1 · Accepted Answer · answered Jan 15 '16 at 16:27

1

The problem is the floating point column. You can review the issues with this representation and comparisons. One place to start is the documentation.

Great, we know it doesn't work. More importantly, what can you do?

Probably the most accurate solution is to switch to decimal, say decimal(5, 1) for the column. Then, this shouldn't be a problem.

Alternatively, use abs() and difference for a fuzzy comparison:

abs(score - 3.4) < 0.0001

answered Jan 15 '16 at 16:27

Gordon Linoff

1,242,037
58
646
786

Looks like this is the issue. I'm going to see if that works. – Callombert Jan 15 '16 at 16:29
@gordon-lindoff and that is in fact correct. thanks for the help – Callombert Jan 15 '16 at 16:31

Deleting a row based on a float column in Mysql/PHP?

1 Answers1