We help IT Professionals succeed at work.

PHP Range Query Question/Problem

jeveloff2
jeveloff2 asked
on
914 Views
Last Modified: 2009-07-29
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!
Comment
Watch Question

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.Group MD
CERTIFIED EXPERT

Commented:
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')


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.
@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.

Author

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?
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

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.

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.