asked on

PHP Range Query Question/Problem

I have a script which searches vehicles in our MYSQL database and am trying to add a price range search. The issue is if I enter 2000 (as the from price) and 3000 (as the to price), numbers higher than 3,000 are displayed if they contain the starting character (such as 10,995). Here is the code I'm using to query:

      if($fPrice != "" && $tPrice != "")
      {
            $priceQuery=" AND varListPrice between '$fPrice' and '$tPrice'";
      }

I'm very new to PHP and would really appreciate any help or opinions as to what's going wrong. Thanks!

DiscoNova

You are in essence comparing numeric values using string literals (since you're putting them inside single quotes). This is like saying "WHERE something BETWEEN 'X' AND 'Y'", which logically means nearly nothing, and in SQL it's meaning ain't as straightforward as you'd think.

You should convert the $fPrice and $tPrice into numbers and use them instead, and that way they would start working. As a suggestion; try going with integer values with the numbers only. They are a lot easier to handle than attempting to handle the multitude of other decimal numeric interpretations, some with thousand separators, decimal separators that might be either comma or colon, etc.

Graham N.

This should handle what you want to do - it assumes fPrice is the lower price and tPrice is the higher (top) price:

$priceQuery="SELECT * FROM `yourtablename` WHERE (`varListPrice`>='$fPrice' AND `varListPrice`<='$tPrice')";

Yu can insert more selection criteria after the where i.e WHERE `fieldname`='$variable' AND (`varListPrice`>='$fPrice' AND `varListPrice`<='$tPrice')

DiscoNova

One more thing (I'm beginning to sound Matlock) ... you should never include variables directly into SQL-query. At least if the variables are coming from the end users. That way you are vulnerable to SQL injection (for example, someone could provide the $fPrice as "'; DELETE FROM USERS; --" and I promise you wouldn't like that.

DiscoNova

@grahamnonweiler: Two mistakes. Still comparing apples to oranges (using string literals instead of numeric values). Also, I wouldn't change the highly optimized (internally in the MySQL engine) single BETWEEN-predicate to two distinct comparison-predicates; that damages scalability.

jeveloff2

ASKER

@ DiscoNova: Thank you for all of your sugestions so far, they are making a lot of sense. When you said "convert the $fPrice and $tPrice into numbers" I'm a little confused as to what you mean. All of the rows in varListPrice are numbers (no letters orcommas), although the type is VARCHAR. I will go back and change it to integer, but should that fix the range problem?

ASKER CERTIFIED SOLUTION

DiscoNova

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

jeveloff2

ASKER

Thank you very much! I'm not running MYSQL 5, so the second worked perfecly! Also, thank you for explaining the reasoning behind the answer.