dcsimg
Results 1 to 3 of 3

Thread: MySQL Query

  1. #1
    Join Date
    Dec 2004
    Posts
    32

    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
    Feb 2003
    Posts
    1,048
    So basically your query is looking for "AND property_freezer = 'no'".

    Have you checked the database to see if the data has 'no' in that field? If not, then what does it have?

    Personally, I would do it like below (forgive me if I get the code wrong, I'm not a PHP programmer). This way, you eliminate any fields not checked from the search at all. This will make your searches much quicker!!

    Plus, my company does data/web services exclusively for real estatea brokers and MLS's, so I have an understanding of the data that you are working with and someone not checking freezer when they perform a search does not mean that they are looking for a place that absolutely does not have one. It means that they are not requiring that it have one. Your search results will be more accurate.


    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'];

    //Begin query
    $Query = "SELECT * FROM property WHERE property_type = '$property_type' AND property_location = '$property_location' AND property_rent <= '$property_rent' ";

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


    // Search info from the Database.
    $result = mysql_query("$Query")
    or die("SELECT Error: ".mysql_error());

  3. #3
    Join Date
    Dec 2004
    Posts
    32
    Thank you very much, copy and pasted the code and it works great.

    Many thanks Rawhide!!!

Posting Permissions

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