• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 900
  • Last Modified:

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!
0
jeveloff2
Asked:
jeveloff2
  • 4
  • 2
1 Solution
 
DiscoNovaCommented:
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.
0
 
grahamnonweilerCommented:
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')


0
 
DiscoNovaCommented:
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.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
DiscoNovaCommented:
@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.
0
 
jeveloff2Author Commented:
@ 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?
0
 
DiscoNovaCommented:
The actual problem is that when the database engine is comparing strings (which of them is "larger" than the other), it does not have the knowledge it might otherwise have. For example:

If they were numbers, it would *know* that between 1000 and 2000 there are exactly 999 integer values. But because they are characters, the engine will attempt to convert them into something it can really compare. It might take the character codes of the characters to be the comparison criteria (or something else, binary value in the worst example) where, depending on the character set, zero *might* ordered "above" nine, meaning that the ordering is incorrect, and saying "where value between 0 and 9" would give no results, because there possibly are no values between 0 and 9 :)

Using the wrong datatype in queries means that you're taking knowledge away from the database engine which it could use to perform a lot better.

Perhaps the first thing (before starting to change the database) I would do is try how things would work if the values were "numbers". This can be done by modifying the original query you had like this (more or less, I didn't really test this) ...

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

... if you're not running MySQL 5, you could try ...

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

... this should ascertain that the values are handled as numbers in the query instead of strings (though, the database engine will be doing a lot of unnecessary work converting the values in the table into numbers on-the-fly, which it wouldn't be doing if they were numbers to begin with).

Test this out and tell how it goes. It is still possible, that this doesn't actually help in the matter, and that there might still be something wrong, but this is something that immediately stroke my eye in the query (and you may believe me, I've met this problem during a multitude of late night debugging sessions :)
0
 
jeveloff2Author Commented:
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.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now