Page 1 of 1

am having problem with my search mysql script, pls help

Posted: Mon Aug 06, 2012 11:10 am
by adsegzy
Hello pls am having problem with my script and its like this.
I am developing a property site where visitors can search for property. I have 5 dropdowns in the search script, eg as below:

STATE (dropdown)
All
New York
Los Angeles
Ohio

CITY (dropdown)
All
(the remaining list of cities here will be determined by the state selected above)

Type (dropdown)
Sales
Buy

MIN PRICE (dropdown)
All
$500
$2,000
$10,000
$500,000

MAX PRICE (dropdown)
All
$500
$2,000
$10,000
$500,000

If the visitor selected all in all the 4 fields (exept type field), I search my table as below;

$search="select * from property where type='$type'";
$query=mysql_query($search)

The above worked fine, but how will i put teh script when some are 'ALL' and some are not 'ALL'

I tried the below but it didn't word

Code: Select all

if($state=='All') {$state2="";} else {$state2="state='$state' ";}
					
					if(($state=="All") && ($city != "All")) {$city2= "city = '$city' ";}
					elseif(($state!="All") && ($city != "All")) {$city2= "and city = '$city' ";}
					else $city2="";
					
					if(($state=="All") && (city == "All")) {$type2 .= "type = '$type' ";}
					else {$type2= "and type = '$type' ";}
					
					if($property=='All') $property2=""; else  $property2="and property = '$property' ";
					
					if($min_price=='All') $min_price2=""; else  $min_price2="and min_price => '$min_price' ";
					
					if($max_price=='All') $max_price2=""; else  $max_price2="and max_price <= '$max_price' ";
					
					 $where = $state2."".$city2."".$type2."".$property2."".$min_price."".$max_price;
			 
$select="select * from property where $where order by property_id desc";
						  $query = mysql_query($sql_select);
I also use this

Code: Select all

$type2 = "type='$type' and ";
					 if($state=='All') {$state2==""; $city2=="";}
					 elseif($state!='All' && $city=="All") $state2=" state='$state' and";
					 else $state2=" state='$state' and  city='$city' and ";
					
					 if($property=='All') $property2==""; 
					 elseif($property=='All' && $min_price!='All') $property2=="property='$property' and ";
					 else $property2="property='$property'";
					 
					 
					 if($min_price=='All') $min_price2=="";
					 elseif($min_price=='All' && $max_price!="All") $min_price2=="min_price='$min_price' and ";
					 else $min_price2="min_price='$min_price'";
					 
					 
					 if($max_price=='All') $max_price2=="";
					 elseif($max_price=='All' && $bedroom!='any') $max_price2=="max_price='$max_price' and ";
					 else $max_price2="max_price='$max_price'";
					 
					 
					 if($bedroom=='any') $bedroom2=="";
					 elseif($bedroom!='any') $bedroom2=="bedroom='$bedroom' and ";
					 else $bedroom2="bedroom='$bedroom'";
					 
					 
					 if($bathroom=='any') $bathroom2=="";
					 else $bathroom2=" and bathroom='$bathroom'";
					 
					 $where = "where ";
					 $where .= $state2;
					 $where .= $city2;
					 $where .= $min_price2;
					 $where .= $type2;
					 $where .= $property2;
					 $where .= $max_price2;
					 $where .= $bedroom;
					 $where .= $bathroom;
						
						$sql_select="select * from property $where order by property_id desc LIMIT $from,$max_results";
						$query = mysql_query($sql_select);
Pls what can I do?

Re: am having problem with my search mysql script, pls help

Posted: Tue Aug 07, 2012 4:00 am
by social_experiment
For an 'all' selection from either state or city (and all the fields i guess) you will have to remove it from the query because people want to see everything in a specific state / city / price

Code: Select all

<?php
 $conditionalAry = array();

 // the basis of the query is constant since you want to select certain fields,
 // only the "WHERE" criteria will be changing.
 $qry = "SELECT fields FROM table ";
 
 if ($state != 'All')  {
     $conditionalAry[] = "stateField = '" . $state . "' ";
 }
 //
 if ($city != 'All') {
    $conditionalAry[] = "cityField = '" . $city . "' ";
 }
 //
 if ($min_price != 'All') {
    $conditionalAry[] = "minPriceField >= '" . $min_price . "' ";
 }
 //
 if ($max_price != 'All') {
    $conditionalAry[] = "maxPriceField <= '" . $max_price . "' ";
 }

 // now $conditionalAry should have values depending on what was selected
 // but you only want to use $conditionalAry if it has at least 1 value inside 
 if (count($conditionalAry) > 0) {
    $qry  .= "WHERE " . implode(' AND ', $conditionalAry);
 }

 $sql = mysql_query($qry);
?>
Hth.