CODEWITHSUNDEEP
mysqlperlsearchrange
t a
t a

Reputation: 201

Price low and High search

I'm using the code snipet below to search price ranges. If I search 1-600 dollars as low-high, results include prices like 22,500. The column is varchar. Thanks

if (($price_low) && ($price_high)){
$statement .= " OR item_price BETWEEN ? AND ? ";
push(@binds,$price_low,$price_high);
}
elsif (($price_low) && ($price_high eq "")){
$statement .= " OR item_price > ? ";
push(@binds,$price_low);
}
elsif (($price_high) && ($price_low eq "")){
$statement .= " OR item_price BETWEEN ? AND ? ";
push(@binds,1,$price_high);
}
else {  }
my $sth = $dbh->prepare(qq(SELECT * FROM ads WHERE $statement )) or  die $DBI::errstr;
$sth->execute(@binds);

Upvotes: 0

Views: 238

Answers (1)

pilcrow
pilcrow

Reputation: 58589

You write:

The column is varchar.

That, and your formatting within the column, is the problem here. For the row you mention, you are essentially doing this:

> SELECT '22,500' BETWEEN 1 AND 600;

MySQL will forcibly convert the string '22,500' to a numeric value, chopping off everything after (and including) the first comma. So, an item_price of '22,500' becomes 22, which of course is BETWEEN 1 AND 600. (What happens if you SHOW WARNINGS after you execute your statement? I think you'll see a warning about incorrect DOUBLE truncation...)

Convert your column to a bona fide numeric type, and try your query again. (Fixing the issue @CanSpice pointed out...)

Upvotes: 3

Related Questions