am having problem with my search mysql script, pls help

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
adsegzy
Forum Contributor
Posts: 184
Joined: Tue Jul 28, 2009 9:26 am

am having problem with my search mysql script, pls help

Post 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?
User avatar
social_experiment
DevNet Master
Posts: 2793
Joined: Sun Feb 15, 2009 11:08 am
Location: .za

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

Post 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.
“Don’t worry if it doesn’t work right. If everything did, you’d be out of a job.” - Mosher’s Law of Software Engineering
Post Reply