So we need to build a SELECT [mysql.com] statement based on form data If you take a quick look at the syntax it des seem a little overwhelming. Let's it break it down a little.
SELECT colname1,colname2 FROM table1 WHERE ...
SELECT - this is the command we are issuing. It basically means get some data from our database based on the criteria to follow.
colname1,colname2 - this is the columns we would like to have returned from our table (or tables). This a list of column names seperated by a comma. You can use * to signify all columns.
FROM table1 - this is the table we are getting the data from. You can specify more than one table by listing all of the tables seperated by commas but that would go beyond a basic look at selecting.
WHERE ... - So here is where it gets more complex. The WHERE clause gives criteria for matching the data that we need.
Lets look at a few examples.
SELECT * FROM table1 WHERE name='jatar_k'
This would return all rows where the name column has a value of 'jatar_k'. Often in this case you are looking for a specific row of data. When a value you are searching for is a string you need to surround it with single quotes. If you are selecting an integer you do not need any symbol surrounding it. You can also use wildcards in your string comparisons.
The % means many characters so
... WHERE name='jatar%' - looks for jatar at the beginning of the string
... WHERE name='%jatar' - looks for jatar at the end of the string
... WHERE name='%jatar%' - looks for jatar anywhere in the string
you can also use more than one column value
... WHERE name='jatar_k' AND site='webmasterworld'
This would only return a row where both of the columns matched the values exactly.
You can also use comparison operators [mysql.com] in your WHERE clause to have ranges of data such as >,<,<=,>=,BETWEEN,IN.
Greater than, less than, less than or equal to, greater than or equal to
The above are fairly self explanatory but the others may not be
... WHERE price BETWEEN minvalue AND maxvalue - this will return any values from the specified columns that are between the minimum and maximum
... WHERE value IN (1,2,3,4) - this is for selecting values that are within the specified set. This will only return rows where the values match one of the specified values exactly. Or
... WHERE value NOT IN (1,2,3,4) - this will return values that don't match any of the specified values.
each of the above has its uses.
Another useful part of the SELECT statement is the ability to order your results using
ORDER BY colname1 [DESC¦ASC]
SELECT * FROM table1 ORDER BY colname1 DESC
The ORDER BY clause orders the results by the specified column in either descending or ascending order.
So based on the above we can look at your specific case\
What values do we have?
prop_type - multiple specific selections
area - multiple specific selections
maxprice - integer value to be used in a range
minprice - integer value to be used in a range
So we now know what kind of data we have, how do we get what we need? We will assume your table is named listings and that we want to return all data from each row and use *. We will also assume that the column for house prices is called price and it is an integer column. All other values will be used as the column name.
SELECT * FROM listings WHERE prop_type IN ('','','','') AND area IN ('','','','') AND price BETWEEN $_POST['minprice'] AND $_POST['maxprice']
ok so I left 2 parts out. We need to cycle through the 2 posted arrays prop_type and area to build our IN clause. Let's do that first.
