Hi,
Im building a searchable property database for an estate agent. I built the code no problem but now the property is in the database its brought to light a rather major problem within the search facility.
Basically ive ignored the fact that if using an OR statement in a mysql query if anything is matched from the OR part of the statement the rest of the search feilds are ignored.
Ie.
select * from property where price <='10000' AND prop_type='shop' AND area = 'Hainault' OR area = 'Godmayes';
In actual fact the only way to make this work is like this:
select * from property where price <='10000' AND prop_type='shop' AND area = 'Hainault' OR price <='10000' AND prop_type='shop' AND area = 'Godmayes';
The only real problem is the sheer amount of ares the guy wants it searchable by.
I can't possibly re-write the code to make all possible combinations of OR, because it will be seriously immense.
heres the basic query builder code if anyone has any ideas on how to get round this please help!!
$countquery = "select count(*) from property";
$search_query1 = array();
if (!empty($size_min)) { $search_query1[] = "size >= '$size_min'"; }
if (!empty($size_max)) { $search_query1[] = "size <= '$size_max'"; }
if (!empty($rent_min)) { $search_query1[] = "price >= '$rent_min'"; }
if (!empty($rent_max)) { $search_query1[] = "price <= '$rent_max'"; }
if (!empty($buy_min)) { $search_query1[] = "price >= '$buy_min'"; }
if (!empty($buy_max)) { $search_query1[] = "price <= '$buy_max'"; }
if (!empty($status)) { $search_query1[] = "lease_sale = '$status'"; }
$search_query1 = implode(' AND ', $search_query1);
$search_query = array();
if((!$allareas) && ($advancedsearch == 1)){
if(!empty($tower_hamlets_all)){ $search_query[] = "location = 'TOWER HAMLETS'"; }
if((empty($tower_hamlets_all)) && (!empty($bow))){ $search_query[] = "area = 'BOW'"; }
if((empty($tower_hamlets_all)) && (!empty($bethnalgreen))){ $search_query[] = "area = 'BETHNAL GREEN'"; }
if((empty($tower_hamlets_all)) && (!empty($limehouse))){ $search_query[] = "area = 'LIMEHOUSE'"; }
if((empty($tower_hamlets_all)) && (!empty($mileend))){ $search_query[] = "area = 'MILE END'"; }
if((empty($tower_hamlets_all)) && (!empty($whitechapel))){ $search_query[] = "area = 'WHITECHAPEL'"; }
if((empty($tower_hamlets_all)) && (!empty($poplar))){$search_query[] = "area = 'POPLAR'";}
if((empty($tower_hamlets_all)) && (!empty($isleofdogs))){ $search_query[] = "area = 'ISLE OF DOGS'";}
if(!empty($newham_all)){ $search_query[] = "location = 'NEWHAM'"; }
if((empty($newham_all)) && (!empty($westham))){ $search_query[] = "area = 'WEST HAM'"; }
if((empty($newham_all)) && (!empty($eastham))){ $search_query[] = "area = 'EAST HAM'"; }
if((empty($newham_all)) && (!empty($plaistow))){ $search_query[] = "area = 'PLAISTOW'"; }
if((empty($newham_all)) && (!empty($beckton))){ $search_query[] = "area = 'BECKTON'";}
if((empty($newham_all)) && (!empty($canningtown))){ $search_query[] = "area = 'CANNING TOWN'"; }
if((empty($newham_all)) && (!empty($silvertown))){ $search_query[] = "area = 'SILVERTOWN'"; }
if((empty($newham_all)) && (!empty($stratford))){ $search_query[] = "area = 'STRATFORD'"; }
if((empty($newham_all)) && (!empty($docklands))){ $search_query[] = "area = 'DOCKLANDS'"; }
if(!empty($hackney_all)){ $search_query[] = "location = 'HACKNEY'"; }
if((empty($hackney_all)) && (!empty($hackney))){ $search_query[] = "area = 'HACKNEY'"; }
if((empty($hackney_all)) && (!empty($hackneywick))){ $search_query[] = "area = 'HACKNEY WICK'"; }
if((empty($hackney_all)) && (!empty($shoreditch))){ $search_query[] = "area = 'SHOREDITCH'"; }
if((empty($hackney_all)) && (!empty($dalston))){ $search_query[] = "area = 'DALSTON'"; }
if((empty($hackney_all)) && (!empty($stokenewington))){ $search_query[] = "area = 'STOKE NEWINGTON'"; }
if((empty($hackney_all)) && (!empty($clapton))){ $search_query[] = "area = 'CLAPTON'"; }
if((empty($hackney_all)) && (!empty($stamfordhill))){ $search_query[] = "area = 'STAMFORD HILL'"; }
if(!empty($walthamforest_all)){ $search_query[] = "location = 'WALTHAM FOREST'"; }
if((empty($walthamforest_all)) && (!empty($walthamstow))){ $search_query[] = "area = 'WALTHAMSTOW'"; }
if((empty($walthamforest_all)) && (!empty($chingford))){ $search_query[] = "area = 'CHINGFORD'"; }
if((empty($walthamforest_all)) && (!empty($leyton))){ $search_query[] = "area = 'LEYTON'"; }
if((empty($walthamforest_all)) && (!empty($leytonstone))){ $search_query[] = "area = 'LEYTONSTONE'"; }
if(!empty($redbridge_all)){ $search_query[] = "location = 'REDBRIDGE'"; }
if((empty($redbridge_all)) && (!empty($ilford))){ $search_query[] = "area = 'ILFORD'"; }
if((empty($redbridge_all)) && (!empty($sevenkings))){ $search_query[] = "area = 'SEVEN KINGS'"; }
if((empty($redbridge_all)) && (!empty($chadwellheath))){ $search_query[] = "area = 'CHADWELL HEATH'"; }
if((empty($redbridge_all)) && (!empty($goodmayes))){ $search_query[] = "area = 'GOODMAYES'"; }
if((empty($redbridge_all)) && (!empty($hainault))){ $search_query[] = "area = 'HAINAULT'"; }
if((empty($redbridge_all)) && (!empty($gantshill))){ $search_query[] = "area = 'GANTS HILL'"; }
if((empty($redbridge_all)) && (!empty($redbridge))){ $search_query[] = "area = 'REDBRIDGE'"; }
if((empty($redbridge_all)) && (!empty($woodfordgreen))){ $search_query[] = "area = 'WOODFORD GREEN'"; }
if((empty($redbridge_all)) && (!empty($southwoodford))){ $search_query[] = "area = 'SOUTH WOODFORD'"; }
if((empty($redbridge_all)) && (!empty($wanstead))){ $search_query[] = "area = 'WANSTEAD'"; }
if(!empty($havering_all)){ $search_query[] = "location = 'HAVERING'"; }
if((empty($havering_all)) && (!empty($romford))){ $search_query[] = "area = 'ROMFORD'"; }
if((empty($havering_all)) && (!empty($hornchurch))){ $search_query[] = "area = 'HORNCHURCH'"; }
if((empty($havering_all)) && (!empty($collierrow))){ $search_query[] = "area = 'COLLIER ROW'"; }
if((empty($havering_all)) && (!empty($haroldhill))){ $search_query[] = "area = 'HAROLD HILL'"; }
if((empty($havering_all)) && (!empty($upminster))){ $search_query[] = "area = 'UPMINSTER'"; }
if((empty($havering_all)) && (!empty($rainham))){ $search_query[] = "area = 'RAINHAM'"; }
if(!empty($barking)){ $search_query[] = "area = 'BARKING'"; }
if(!empty($dagenham)){ $search_query[] = "area = 'DAGENHAM'"; }
if(!empty($epping_all)){ $search_query[] = "location = 'EPPING'"; }
if((empty($epping_all)) && (!empty($loughton))){ $search_query[] = "area = 'LOUGHTON'"; }
if((empty($epping_all)) && (!empty($buckhirsthill))){ $search_query[] = "area = 'BUCKHIRST HILL'"; }
if((empty($epping_all)) && (!empty($chigwell))){ $search_query[] = "area = 'CHIGWELL'"; }
if((empty($epping_all)) && (!empty($epping))){ $search_query[] = "area = 'EPPING'"; }
if(!empty($enfield)){ $search_query[] = "area = 'ENFIELD'"; }
if(!empty($haringey)){ $search_query[] = "area = 'HARINGEY'"; }
if(!empty($islington)){ $search_query[] = "area = 'ISLINGTON'"; }
if(!empty($london)){ $search_query[] = "area = 'CITY OF LONDON'"; }
if(!empty($camden)){ $search_query[] = "area = 'CAMDEN'"; }
}
$search_query = implode(' OR ', $search_query);
$search_query2 = array();
if(!$advancedsearch){
if (!empty($prop_type)) { $search_query2[] = "type='$prop_type' OR type2='$prop_type'"; }
}
elseif(($advancedsearch == 1) && (empty($prop_type_all))){
if (!empty($prop_type_shop)) { $search_query2[] = "type='Shop'"; }
if (!empty($prop_type_ind)) { $search_query2[] = "type='Ind/Whse'"; }
if (!empty($prop_type_office)) { $search_query2[] = "type='Office'"; }
if (!empty($prop_type_land)) { $search_query2[] = "type='Land'"; }
if (!empty($prop_type_investment)) { $search_query2[] = "type='Investment'"; }
if (!empty($prop_type_development)) { $search_query2[] = "type='Development'"; }
if (!empty($prop_type_leisure)) { $search_query2[] = "type='Leisure'"; }
if (!empty($prop_type_nursery)) { $search_query2[] = "type='Nursery'"; }
if (!empty($prop_type_educational)) { $search_query2[] = "type='Educational'"; }
if (!empty($prop_type_shop)) { $search_query2[] = "type2='Shop'"; }
if (!empty($prop_type_ind)) { $search_query2[] = "type2='Ind/Whse'"; }
if (!empty($prop_type_office)) { $search_query2[] = "type2='Office'"; }
if (!empty($prop_type_land)) { $search_query2[] = "type2='Land'"; }
if (!empty($prop_type_investment)) { $search_query2[] = "type2='Investment'"; }
if (!empty($prop_type_development)) { $search_query2[] = "type2='Development'"; }
if (!empty($prop_type_leisure)) { $search_query2[] = "type2='Leisure'"; }
if (!empty($prop_type_nursery)) { $search_query2[] = "type2='Nursery'"; }
if (!empty($prop_type_educational)) { $search_query2[] = "type2='Educational'"; }
}
$search_query2 = implode(' OR ', $search_query2);
if (!empty($search_query1)) { $countquery .= " WHERE ".$search_query1; }
if ((empty($search_query)) && (!empty($search_query))) { $countquery .= " WHERE ".$search_query; }
if (!empty($search_query)) { $countquery .= " AND ".$search_query; }
if ((empty($search_query1)) && (!empty($search_query2))) { $countquery .= " WHERE ".$search_query2; }
if (!empty($search_query2)) { $countquery .= " AND ".$search_query2; }
echo $countquery;
$countresult = mysql_query($countquery);
Thanks in advance!
