Show all bedrooms or cities in property site query

BrighteyesDesign
BrighteyesDesign used Ask the Experts™
on
On a property site the user can search selecting various criteria: city, bedrooms, pricemin and pricemax.

All works fine if you select a particular city or bedroom but want I want is the ability to search all bedrooms or/and all cities. Any ideas how i'd do this?

The price part is fine, for pricemin for the all label I use the value '0' and for pricemax '5000000000000' so all results from any price show.

This is the code currently...

$country_code = $_GET["intsearch"];
$bedrooms = $_GET["bedrooms"];
$min = $_GET["min"];
$max = $_GET["max"];
$cityCode = $_GET["city"];


SELECT propertyID, customer, letby, additionalimage1, additionalimage2, additionalimage3, additionalimage4, additionalimage5, additionalimage6, additionalimage7, additionalimage8, additionalimage9, additionalimage10, address, bathrooms, bedrooms, brochure, details, floorPlan1, floorPlan2, floorPlan3, floorplan4, fullPostCode, furnished, groundRent, HIPDocument, leaseholdYearsRemaining, mainimage, name, prices, pricePrefix, propertyType, receptionRooms, regionCode, saleOrRent, fullPath, planbutton, google, prices2, pricePrefix2, `currency`, epcbutton, bullets, currency2, country_code, streetview, cityCode FROM properties WHERE country_code = %s   AND cityCode = '$cityCode' AND bedrooms='$bedrooms' AND prices BETWEEN '$min' AND '$max' ORDER BY prices ASC

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
before you go any further, you have got to sanitize your inputs. you are susceptible to sql injection. See: http://www.tizag.com/mysqlTutorial/mysql-php-sql-injection.php; as a hint, use php's mysql escape string commands.

Anyway, on you your question:

This is your where clause:

WHERE country_code = %s   AND cityCode = '$cityCode' AND bedrooms='$bedrooms' AND prices BETWEEN '$min' AND '$max' ORDER BY prices ASC

you can remove any criteria you want. to search all bedrooms, in all cities, you'd have

WHERE prices BETWEEN '$min' AND '$max' ORDER BY prices ASC

In order to compose your where clause programmatically, you'd do something like this:

$where_clause = "WHERE ";

if (!empty($_GET['intsearch']))
  $where_clause .= " COUNTRY_CODE = '" . mysql_real_escape($_GET['intsearch']) ."' AND ";

if (!empty($_GET['city']))
  $where_clause .= " cityCode = '" . mysql_real_escape($_GET['city']) ."' AND ";

if (!empty($_GET['bedrooms']))
  $where_clause .= " bedrooms = '" . mysql_real_escape($_GET['bedrooms']) ."' AND ";

$where_clause .= " prices BETWEEN " . intval($_GET['min']) . " AND " .  intval($_GET['min'])

Open in new window

Author

Commented:
Thanks for that, i'll fix that sql injection issue.

Thanks for that code too, how do I integrate it with my current code?

$country_code = $_GET["intsearch"];
$bedrooms = $_GET["bedrooms"];
$min = $_GET["min"];
$max = $_GET["max"];
$cityCode = $_GET["city"];


SELECT propertyID, customer, letby, additionalimage1, additionalimage2, additionalimage3, additionalimage4, additionalimage5, additionalimage6, additionalimage7, additionalimage8, additionalimage9, additionalimage10, address, bathrooms, bedrooms, brochure, details, floorPlan1, floorPlan2, floorPlan3, floorplan4, fullPostCode, furnished, groundRent, HIPDocument, leaseholdYearsRemaining, mainimage, name, prices, pricePrefix, propertyType, receptionRooms, regionCode, saleOrRent, fullPath, planbutton, google, prices2, pricePrefix2, `currency`, epcbutton, bullets, currency2, country_code, streetview, cityCode FROM properties WHERE country_code = %s   AND cityCode = '$cityCode' AND bedrooms='$bedrooms' AND prices BETWEEN '$min' AND '$max' ORDER BY prices ASC

Open in new window


$where_clause = "WHERE ";

if (!empty($_GET['intsearch']))
  $where_clause .= " COUNTRY_CODE = '" . mysql_real_escape($_GET['intsearch']) ."' AND ";

if (!empty($_GET['city']))
  $where_clause .= " cityCode = '" . mysql_real_escape($_GET['city']) ."' AND ";

if (!empty($_GET['bedrooms']))
  $where_clause .= " bedrooms = '" . mysql_real_escape($_GET['bedrooms']) ."' AND ";

$where_clause .= " prices BETWEEN " . intval($_GET['min']) . " AND " .  intval($_GET['min'])

Open in new window

Application Consultant
Commented:
Yes Last remark is correct or:

WHERE country_code = %s  
     AND ( cityCode = '$cityCode' or '$cityCode' = '')
     AND (bedrooms='$bedrooms' or '$bedrooms' = '')
     AND prices BETWEEN '$min' AND '$max'
 ORDER BY prices ASC
to integrate with your current code, you just plug that where clause with your select into the mysql statment, something like this:

where_clause = "WHERE ";

if (!empty($_GET['intsearch']))
  $where_clause .= " COUNTRY_CODE = '" . mysql_real_escape($_GET['intsearch']) ."' AND ";

if (!empty($_GET['city']))
  $where_clause .= " cityCode = '" . mysql_real_escape($_GET['city']) ."' AND ";

if (!empty($_GET['bedrooms']))
  $where_clause .= " bedrooms = '" . mysql_real_escape($_GET['bedrooms']) ."' AND ";

$where_clause .= " prices BETWEEN " . intval($_GET['min']) . " AND " .  intval($_GET['min']);

mysql_query("SELECT propertyID, customer, letby, additionalimage1, additionalimage2, additionalimage3, additionalimage4, additionalimage5, additionalimage6, additionalimage7, additionalimage8, additionalimage9, additionalimage10, address, bathrooms, bedrooms, brochure, details, floorPlan1, floorPlan2, floorPlan3, floorplan4, fullPostCode, furnished, groundRent, HIPDocument, leaseholdYearsRemaining, mainimage, name, prices, pricePrefix, propertyType, receptionRooms, regionCode, saleOrRent, fullPath, planbutton, google, prices2, pricePrefix2, `currency`, epcbutton, bullets, currency2, country_code, streetview, cityCode FROM properties " . $where_clause);
Theo KouwenhovenApplication Consultant

Commented:
Yes Last remark is correct or:

mysql_query("SELECT propertyID, customer, letby, additionalimage1, additionalimage2, additionalimage3, additionalimage4, additionalimage5, additionalimage6, additionalimage7, additionalimage8, additionalimage9, additionalimage10, address, bathrooms, bedrooms, brochure, details, floorPlan1, floorPlan2, floorPlan3, floorplan4, fullPostCode, furnished, groundRent, HIPDocument, leaseholdYearsRemaining, mainimage, name, prices, pricePrefix, propertyType, receptionRooms, regionCode, saleOrRent, fullPath, planbutton, google, prices2, pricePrefix2, `currency`, epcbutton, bullets, currency2, country_code, streetview, cityCode FROM properties
WHERE country_code = % AND ( cityCode = '$cityCode' or '$cityCode' = '')  AND  bedrooms='$bedrooms' or '$bedrooms' = '') AND prices BETWEEN '$min' AND '$max'
 ORDER BY prices ASC");

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial