PHP mySQL Query ignores decimal values

Question

I'm using AJAX to pull certain stocks from a database based on the values that are contained on an input. One of these values would be average returns, which contains a decimal value.

I have two variables, $min_returns and $max_returns that hold values such as 4 and 6.6 respectively (normally hold the AJAX return).

PHP code:

SELECT avgreturns 
FROM stocks 
WHERE avgreturns >= '$min_returns' AND avgreturns <= '$max_returns'

In the database, the values will be anything from 0.3 to 4.6, etc..

The problem that I am getting is that the decimals may as well not be there. For example, 1.3 will be valued as 13, which wouldn't be included in my example above. Integers, like 5, are read properly and would be included above.

I am new to PHP and mySQL all together. Is there something that I am doing wrong in the database, where I need to set the columns to a correct setting? I know that everything called in from a query is returned as a string, but there isn't any way to convert that within the query, is there?

Would like to avoid rounding the values in my database if at all possible.

possible duplicate of [Show 2 decimal places without rounding off](http://stackoverflow.com/questions/9667413/show-2-decimal-places-without-rounding-off) — a coder, Apr 26 '15 at 06:41
See: https://dev.mysql.com/doc/refman/5.0/en/fixed-point-types.html — a coder, Apr 26 '15 at 06:43

Mureinik · Accepted Answer · 2015-04-27T05:48:33.953

Single quotes denote string literals, so you're forcing the database to compare the values lexicographically. In order to interpret them numerically, just drop the quotes:

SELECT avgreturns 
FROM   stocks 
WHERE  avgreturns >= $min_returns AND avgreturns <= $max_returns

Mandatory warning:
Using string manipulation to create a query is usually a bad idea which leaves you vulnerable to SQL injection attacks. You should probably use a prepared statement instead.

PHP mySQL Query ignores decimal values

1 Answers1