如何使用多个WHERE子句执行MySQL查询？

Question

how would you do a mysql query where a user can choose from multiple options. Fox example I have a form that user can use to search for houses. Now I have a select box where you can chosse whether you want a house, a flat or whatever. Then I have a second box where you can choose for example the city you want the house or flat to be in. And maybe another one with the maximum price.

Now how would you do the mysql query? My problem is, I would do it like that: if($_POST["house_type"] != 0) { $select = mysql_query("SELECT * FROM whatever WHERE type = '".$_POST["house_type"]."'"); } But now I only have the case that someone has chosen a house type but not any other option. So do I have to do an "if" for every possible combination of selected elements?

To emphasize my problem:

if(!isset($_POST["house_type"])) {

            if($_POST["something"] == 0) {
        $search_select = @mysql_query("SELECT * FROM housedata WHERE something = $_POST["whatever"]);

            }
            elseif($_POST["something"] != 0) {
        $search_select = @mysql_query("SELECT * FROM housedata  something = $_POST["whatever"] AND somethingelse = 'whatever');
            }
        }



elseif(!isset($_POST["house_type"])) {


        if($_POST["something"] == 0) {
        $search_select = @mysql_query("SELECT * FROM housedata WHERE something = $_POST["whatever"]);

            }
            elseif($_POST["something"] != 0) {
        $search_select = @mysql_query("SELECT * FROM housedata  something = $_POST["whatever"] AND somethingelse = 'whatever');
            }
        }

Now imagine I had like 10 or 20 different select boxes, input fields and checkboxes and I would have to do a mysql query depending on what of these boxes and fiels and checkboxes is filled. This would be a code that is extremely complicated, slow and horrible. So is there a possibility to make a mysql query like:

SELECT * FROM whatever WHERE house_data = '".$whatever."' AND (if(isset($_POST["something"])) { whatever = '".$whatever2."' } AND ...;

You get what I mean? Its a bit complicated to explain but actually its a very important question and probably easy to answer.

Thank you for your help! phpheini

Answer 1

Generate the WHERE clause prior to running the SQL.

A short example:

$whereClause = "";

if ($_POST['opt1']) {
    $opt1 = mysql_real_escape_string($_POST['opt1']);
    $whereClause .= "AND opt1='$opt1'";
}

if ($_POST['opt2']) {
    $opt2 = mysql_real_escape_string($_POST['opt2']);
    $whereClause .= "AND opt2='$opt2'";
}

mysql_query("SELECT * FROM table WHERE 1 ".$whereClause);