如何执行具有多个WHERE子句的MySQL查询? [英] How can execute a MySQL query with multiple WHERE-clauses?

问题描述

您将如何执行mysql查询，用户可以从多个选项中进行选择. Fox的例子我有一个用户可以用来搜索房屋的表格.现在，我有一个选择框，您可以在其中选择是否要房子，公寓还是其他.然后，我有第二个方框，您可以在其中选择例如您想要房屋或公寓所在的城市.也许还有一个价格最高的方框.

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.

现在您将如何进行mysql查询?我的问题是，我会那样做: if($ _ POST ["house_type"]！= 0){ $ select = mysql_query("SELECT * FROM WHERE type ='".$ _ POST ["house_type"].'"); } 但是现在我只遇到有人选择房屋类型而没有其他选择的情况.那么我是否必须对所选元素的每种可能组合都做一个"if"操作?

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?

要强调我的问题:

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');
            }
        }

现在想象一下，我喜欢10或20个不同的选择框，输入字段和复选框，而我将不得不根据这些框和填充的内容进行mysql查询.这将是极其复杂，缓慢且可怕的代码.所以有可能进行如下的mysql查询:

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.

谢谢您的帮助！ phpheini

Thank you for your help! phpheini

推荐答案

在运行SQL之前生成WHERE子句.

Generate the WHERE clause prior to running the SQL.

一个简短的例子:

$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); 

这篇关于如何执行具有多个WHERE子句的MySQL查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！