Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2003
    Location
    Roanoke, VA, US
    Posts
    390

    building SQL query with PHP based on user selection

    I have a form that has five checkboxes. Depending on what the user selects, I need to change the query.

    My basic query is this--

    $result = mysql_query("SELECT * FROM $table WHERE latitude IS NOT NULL AND longitude IS NOT NULL AND latitude >= $minLat AND latitude <= $maxLat AND longitude >= $minLng AND longitude <= $maxLng");

    Now, I need to add on to the end of it code like this for each checkbox that is checked--

    AND listprice BETWEEN '0' AND '100000' OR listprice BETWEEN '150000' AND '250000' etc...

    If checkbox '0' is selected, then I need--> listprice BETWEEN '0' AND '100000'
    If checkbox '3' is selected, then I need --> listprice BETWEEN '150000' AND '250000'

    That's easy enough, but how do I get the OR in there between each of those without leaving a trailing OR at the end and fouling up the query. What I need it to look like (depending on what the user selects) is--

    $result = mysql_query("SELECT * FROM $table WHERE latitude IS NOT NULL AND longitude IS NOT NULL AND latitude >= $minLat AND latitude <= $maxLat AND longitude >= $minLng AND longitude <= $maxLng AND listprice BETWEEN '0' AND '100000' OR listprice BETWEEN '150000' AND '250000'")


    I hope that made sense. I see maybe the need for a loop but I don't know where to begin. Each checkbox is given the value of '1' if it is checked or '0' if it is off. I also have a variable called $arguments that is the sum of all the checkboxes so if there are 3 checked, the value of $arguments is '3'.

    Any help appreciated!

  2. #2
    Join Date
    Aug 2000
    Posts
    2,749
    Why not put the queries into an if statement? or use switch?

  3. #3
    Ya, a switch case would be very easy to setup
    TextAdMarket - http://www.TextAdMarket.com
    Supply & Demand Advertising

  4. #4
    Join Date
    Jan 2003
    Posts
    1,715
    I believe his issue is this:
    If checkbox '0' is selected then
    $append .= "listprice BETWEEN '0' AND '100000'"
    If checkbox '3' is selected then
    $append .= "listprice BETWEEN '150000' AND '250000'"

    Then the problem is getting the ORs in the right places. For that, I would put the extra conditions into an array ($append[] = ...) and join() them. You would also need an 'if' to handle getting the AND in there. This whole process is curious, though, since I question if someone would really look at those ranges, but have no interest in the 100k - 150k range. Perhaps dropdowns for each end of the range would be better than checkboxes.
    Game Servers are the next hot market!
    Slim margins, heavy support, fickle customers, and moronic suppliers!
    Start your own today!

  5. #5
    Join Date
    Jul 2003
    Location
    Kuwait
    Posts
    5,099
    I'm curious why you are checking for NOT NULL, if you have an additional range check. One negates the other.
    In order to understand recursion, one must first understand recursion.
    If you feel like it, you can read my blog
    Signal > Noise

  6. Newsletters

    Subscribe Now & Get The WHT Quick Start Guide!

Related Posts from theWHIR.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •