Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2005
    Posts
    14

    Unanswered: select different search options (was "MySQL query")

    Hi, i have a search page, where the user can select different options about a property to search for.

    If the user selected fridge and wmachine, the database would display only the properties that have a fridge and a wmachine assuming that a property does have both. If an item in the database has a fridge, wmachine and a freezer, no results would be displayed if the user searched for a fridge and a wmachine as the user didn't select freezer, even though the property does contain the fridge and the wmachine. How would i change my query to solve this?

    Below is the code that searches the database:-

    include 'db.php';
    // Define post fields into simple variables
    $property_type = $_GET['property_type'];
    $property_location = $_GET['property_location'];
    $property_rent = $_GET['property_rent'];
    $property_lounge = $_GET['property_lounge'];
    $property_dwasher = $_GET['property_dwasher'];
    $property_mwave = $_GET['property_mwave'];
    $property_fridge = $_GET['property_fridge'];
    $property_freezer = $_GET['property_freezer'];
    $property_wmachine = $_GET['property_wmachine'];
    $property_tdryer = $_GET['property_tdryer'];
    $property_bath = $_GET['property_bath'];
    $property_shower = $_GET['property_shower'];
    $property_garage = $_GET['property_garage'];
    $property_garden = $_GET['property_garden'];
    $property_smoking = $_GET['property_smoking'];
    $property_pets = $_GET['property_pets'];
    $property_daccess = $_GET['property_daccess'];
    $property_tvpoint = $_GET['property_tvpoint'];
    $property_ofparking = $_GET['property_ofparking'];
    $property_furnished = $_GET['property_furnished'];
    $property_pic = $_GET['property_pic'];
    $property_vid = $_GET['property_vid'];
    $property_aud = $_GET['property_aud'];
    $userid = $_GET['userid'];


    if(!$property_lounge){
    $property_lounge = "no";
    }
    if(!$property_dwasher){
    $property_dwasher = "no";
    }
    if(!$property_mwave){
    $property_mwave = "no";
    }
    if(!$property_fridge){
    $property_fridge = "no";
    }
    if(!$property_freezer)
    {
    $property_freezer = "no";
    }
    if(!$property_wmachine){
    $property_wmachine = "no";
    }
    if(!$property_tdryer){
    $property_tdryer = "no";
    }
    if(!$property_bath){
    $property_bath = "no";
    }
    if(!$property_shower){
    $property_shower = "no";
    }
    if(!$property_garage){
    $property_garage = "no";
    }
    if(!$property_garden){
    $property_garden = "no";
    }
    if(!$property_smoking){
    $property_smoking = "no";
    }
    if(!$property_pets){
    $property_pets = "no";
    }
    if(!$property_daccess){
    $property_daccess = "no";
    }
    if(!$property_tvpoint){
    $property_tvpoint = "no";
    }
    if(!$property_ofparking){
    $property_ofparking = "no";
    }
    if(!$property_furnished){
    $property_furnished = "no";
    }


    // Search info from the Database.

    $result = mysql_query( "SELECT * FROM property WHERE
    property_type = '$property_type'
    AND property_location = '$property_location'
    AND property_lounge = '$property_lounge'
    AND property_rent <= '$property_rent'
    AND property_dwasher = '$property_dwasher'
    AND property_mwave = '$property_mwave'
    AND property_fridge = '$property_fridge'
    AND property_freezer = '$property_freezer'
    AND property_wmachine = '$property_wmachine'
    AND property_tdryer = '$property_tdryer'
    AND property_shower = '$property_shower'
    AND property_bath = '$property_bath'
    AND property_shower = '$property_shower'
    AND property_garage = '$property_garage'
    AND property_garden = '$property_garden'
    AND property_smoking = '$property_smoking'
    AND property_daccess = '$property_daccess'
    AND property_tvpoint = '$property_tvpoint'
    AND property_ofparking = '$property_ofparking'
    AND property_furnished = '$property_furnished'
    AND property_pets = '$property_pets'" )
    or die("SELECT Error: ".mysql_error());


    Many Thanks

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    rewrite your script so that you only generate an AND condition into the query for search parameters actually filled in by the user

    hint: start with WHERE 1=1

    see The "any" option in dynamic search SQL (note: site registration may be required, but it's free)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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