[SOLVED] Query with greater than and less than

[Ajpalma]

I have a query that has a selectable ranges letting user select the top and bottom values and it finds the entries between them using greater than and less than

for a value, say 350,000

the top value of 900,000 and bottom of 100,000 will return the correct value.

but a top value of 2,000,000  or a bottom value of 50,000 will return NOTHING.

does anyone know why it does this?or a solution?

 

$result = mysql_query("SELECT * FROM deals WHERE price >= '$sql2' AND price <= '$sql1' ")

 

thanks,

Alex

[mezise]

Hi,

everyting is logically correct. Display query before it is executed and check the syntax.

Maybe there is a space between thousand and million part of 2 ,000,000 price?

[Ajpalma]

nah, it doesn't return the right value even with 50,000 as the bottom limit(even with a 6 digit top limit)

it just seems like it only works when both limits are 6 digits

[mezise]

Debug PHP code and post here the result of variable $displayQuery for problematic range.

$displayQuery = "SELECT * FROM deals WHERE price >= '$sql2' AND price <= '$sql1' ";

print_r($displayQuery);

$result = mysql_query($displayQuery);

[Ajpalma]

SELECT * FROM deals WHERE price >= '50,000' AND price <= '2,000,000'

 

is the result

[mezise]

The comma character might be the problem. Maybe it is configurable in MySQL but I always use default format of numbers: e.g. 1222333 for INT type and 1222333.44 for DOUBLE type.

So change the code into this:

$displayQuery = "SELECT * FROM deals WHERE" .
" price >= '" . preg_replace('/([^0-9])/i', '', $sql2) . "'" .
" AND price <= '" . preg_replace('/([^0-9])/i', '', $sql1) . "' ";
$result = mysql_query($displayQuery);

[Ajpalma]

That was it, Thank you VERY much

[fenway]

What a mess.... store integers as INT.

[mezise]

What a mess.... store integers as INT.

He does. The problem was with not cleaning the data sent from web interface (separation signs).

[fenway]

What a mess.... store integers as INT.

He does. The problem was with not cleaning the data sent from web interface (separation signs).

So he's NOT... integers don't have non-digit characters.