Hai folks,
my property rent website have a search from in the home page like shown below.
Since it has many combinations of user input, i am struggling how to built the MYSQL search query in php 
MySql table name is ‘properties’.
fields :
‘type’ | ‘District’ | ‘city’ | ‘Area’ | ‘Price’ | ‘Bedrooms’ | ‘Bathrooms’
Your GUI may contain the word search, but to me is sounds more like you going to be asking Mysql to filter some fields in your table in your WHERE clause.
First off you need to formulate a couple of queries that actually match data in your test database from a really simple one to a really worst case one. If the data does not exist then enter some in.
Here are some examples of what I mean:
SELECT property_id from properties
WHERE district = 12;
SELECT property_id from properties
WHERE district = 12
AND type = 1
AND bedrooms = 3
AND bathrooms = 1
AND price < 100000
So, do you already have 2 working selects like those above which do bring back positive results which you can test against?
If you have not yet got the queries, then you will need to post the result of this database query;
describe properties
Hai cups,
Thanks for the reply.
i undestand the below query.
SELECT property_id from properties
WHERE district = 12
AND type = 1
AND bedrooms = 3
AND bathrooms = 1
AND price < 100000
but problem is, user might not select all the fields as in the above query. There can be many search patters from that form. This is where i am getting stucked 
ill p
Edited :
describe properties result :
Bad UI, no donut. Seriously, I hate drop down boxes where I might want to select more than one range.
You probably should give two fields for the price and let the user choose the range they wish to seek. Or do “Up to X” and let the user order the results by price in descending or ascending.
Since this is for rental properties I STRONGLY advise including a field for pet policy. Amenities such as Washer / Dryer are also high on the list of search.
Search also needs to include address. What if the customer sees a “for sale” sign on the lawn of the house and then hits your site with an iPhone on the spot - they already know which house…
I’m sorry that none of this makes the task at hand easier, but it is some things to consider.
I’ll put a little more thought into this tonight.
but problem is, user might not select all the fields as in the above query.
I realise that, but that is the challenge that PHP has to address, how to build that query.
Now, there is no use attempting to build the query (with something we will call a query builder) until we know what the query is going to be.
There is no point in building the query until there is some data in the database which returns a positive result when a good query has been built - it is the ultimate confirmation that what you have created actually does work.
So taking my suggestion below as a starting point:
SELECT property_id from properties
WHERE district = 12
AND type = 1
AND bedrooms = 3
AND bathrooms = 1
AND price < 100000
Can you a) rewrite that statement so that it a) matches your data** and b) confirm that you have some data in your database, ideally 2 or more entries, which actually matches that request: ie have a price greater than 100000, more that 3 bedrooms and so on.
**Your type is a varchar, so what is a “type” string you have?
BTW how many Types do you envisage having?
Thanks michel, i ll consider your ui suggessions defently
Hai cups,
alright, here is my first attempt,
$query="SELECT * FROM properties
WHERE type='$type'
OR district='$district'
OR city like '$city%'
OR ((area >= '$minarea') and (area <= '$maxarea'))
OR ((price >= '$minprice') and (price <= '$maxprice'))
OR bedrooms='$bedrooms'
OR bathrooms='$bathrooms'";
it returns what ever records in the table it seems 
Little further improvised :
$query="SELECT * FROM properties
WHERE type='$type'
AND (district='$district'
OR city like '$city%'
OR ((area >= '$minarea') and (area <= '$maxarea'))
OR ((price >= '$minprice') and (price <= '$maxprice'))
OR bedrooms='$bedrooms'
OR bathrooms='$bathrooms')";
it returns what ever records in the table it seems
I think that is because your query is fundamentally flawed.
If I am searching for a house in new york with 3 bedrooms I don’t want you to bring back every house in the US with 3 bedrooms.
Leave the PHP variables out of the equation for a moment, nail some basic SQL queries with real values in order to have a target sql statement.
eg
“…where city = “New York” and bedrooms = 3”
Leave out slightly more complex queries like that below for the moment, we can add them back in later when you have the fundamentals sorted out.
OR ((area >= ‘$minarea’) and (area <= ‘$maxarea’))
What simple query with real values can you put into PhPMyAdmin (or whatever you use to manage your db) which return the a small, correct data set which we can use as a reliable target.
eg
SELECT id, address from properties
WHERE city = "New York"
AND bedrooms = 3
AND bathrooms > 1
In other words just 3 bedroom houses with minimum one bathroom in the city of New York**.
**Either change the name to suit what you have in your db, or enter some spoof test matching data manually - and show me that query, and the result set.
query
SELECT pid, address
FROM properties
WHERE district = "kandy"
AND bedrooms =3
AND bathrooms >3
LIMIT 0 , 30
result :
Great, now try this query for me;
SELECT pid, address
FROM properties
WHERE 1=1
AND district = "kandy"
AND bedrooms =3
AND bathrooms >3
LIMIT 0 , 30
Get the same result?
(forgive me not replicating your entire database )
Well it should work …
Since it has many combinations of user input, i am struggling how to built the MYSQL search query in php
Now, leaving aside how your data is structured, and returning to that basic question in your opening post:
Lets tee a few things up.
We have some target positive data in the database.
You don’t need to fiddle with submitting/resubmitting your form you can recreate the $_POST variables at the top of a script like this;
$_POST['bedrooms'] = '3';
$_POST['bathrooms'] = '3';
$_POST['district'] = 'kandy';
So now, the challenge is to get from the above to this;
SELECT pid, address
FROM properties
WHERE 1=1
AND district = "kandy"
AND bedrooms = 3
AND bathrooms > 3
But first of all, bathrooms is a “greater than”, so either your HTML form stipulates
“more that 3 bathrooms” or just “3 bathrooms” - which is it?
ofcause! same result
Hai cups, thanks for the effort so far,
Bathrooms :
1
2
3
4
5
6
7
8
9
10+
no range, just how many avaiible.
further :
Area :
100 - 1000
1000 - 2000
2000 - 3000
3000+
Price (rent) :
100-1000
1000 - 5000
50000 - 100000
10000+
Well if you pick 3 you mean “with 3 bathrooms”.
SELECT pid, address
FROM properties
WHERE 1=1
AND district = "kandy"
AND bedrooms = 3
AND bathrooms = 3
Well, as the answer to your original question can get very complicated very fast, I am happy to deal with this simple enough query for now.
Your query builder starts off with a string…
$select = "SELECT pid, address FROM properties WHERE 1=1";
to which you add all the incoming POST elements;
(I am leaving out all filtering, error checking and sanitization for the moment)
$and_clauses = "";
foreach( $_POST as $key=>$value){
$and_clauses = " AND $key= '$value'";
}
Put those together and you should have your target query:
echo $select.$and_clauses;
And that is just a little example of how you could make a basic “query builder”.
It is not perfect, it is not secure, it does not cover all of your cases - it is totally dependent upon all of your clauses being this = that, it incorrectly quotes integers, but Mysql forgives that and should run your query all the same.
As is you should be able to add other clauses to the POST array and get differing results.
Have a play with that, think about how your form would have to evolve and change, including the comments about your form made by Michael and come back with your questions.
Thank you, let me try these tips
Slight correction.
Ah, yes, cheers Anthony.
$and_clauses is a string to which you concatenate more strings, I forgot the . before .= ( watch out, leave no space between the . and the = ).
This is a really simplistic example of a query builder, but it shows the basic principle upon which such things can be created.
As an aside, I wonder how well “borrowing” a QueryBuilder implementation from a maintained library would work; simply to build the query required.
Thanks anthony, i ll also have a look at it