MySQL Query

[jackdaniels007]

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

[Rawhide]

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());

[jackdaniels007]

Thank you very much, copy and pasted the code and it works great.

Many thanks Rawhide!!!