expr BETWEEN min AND max

while using expr BETWEEN min AND max to get a minimum and maximum price from the database I'm experiencing some strange output.

QUERY: SELECT DISTINCT make, model, calibre, firearm_action, rrp, rrp BETWEEN 0 AND 750 FROM stock WHERE make LIKE '%WINCHESTER%'

which returns:

RRP: 650
RRP: 650
RRP: 0
RRP: 0
RRP: 0
RRP: 1100
RRP: 0
RRP: 0
RRP: 0

although when I slightly change the query to something more like:

QUERY: SELECT DISTINCT make, model, calibre, firearm_action, rrp, rrp BETWEEN 0 AND 1000 FROM stock WHERE make LIKE '%WINCHESTER%'

The results with RRP of 650 are gone, and yet 1100 appears when it should not (in the first query)

does anyone have an explanation for this? What am I missing?

22 views and no replys 🙁
Can someone tell me if:

A) I'm not making sense.
😎 You just don't know.

Thanks.

I suspect that your problem is that the data type of "rrp" is not a number so MySQL is doing an alphanumeric BETWEEN rather than a numeric one.

"Before MySQL 4.0.5, arguments were converted to the type of expr instead."

That syntax is VERY odd. It should look like this:

SELECT DISTINCT make, model, calibre, firearm_action, rrp, rrp FROM stock WHERE make LIKE '%WINCHESTER%' AND rrp BETWEEN 0 AND 750

Nothing odd with that syntax. It is just a boolean expression.

The output is still wrong. You should either get true or false, not the number or zero.

And the problem here is that rpp looks like it's text, not a numeric or int.

how can I force it to be treated as an integer? I looked through the PHP docs but found nothing.

I changed the datatype on the rrp field to INT(10) as it was set to VARCHAR(10), all seems to be working now.

thanks for the response.