Link to home
Start Free TrialLog in
Avatar of BrighteyesDesign
BrighteyesDesignFlag for Afghanistan

asked on

Show all bedrooms or cities in property site query

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

Avatar of mankowitz
mankowitz
Flag of United States of America image

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

Avatar of BrighteyesDesign

ASKER

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

ASKER CERTIFIED SOLUTION
Avatar of Theo Kouwenhoven
Theo Kouwenhoven
Flag of Netherlands image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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");