Avatar of BrighteyesDesign
BrighteyesDesign
Flag 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

PHPMySQL Server

Avatar of undefined
Last Comment
Theo Kouwenhoven

8/22/2022 - Mon
mankowitz

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

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
Theo Kouwenhoven

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Theo Kouwenhoven

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");
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy