基于输入在函数中调整sql语句 [英] Adjusting sql statement in a function based upon input

问题描述

这有点紧急！

我想做一个简单的过滤器搜索，你可以从一系列的3下拉菜单中选择，然后基于然后显示结果，我将如何调整每个的SQL查询，如果你只选择从一个3而不是所有3等搜索...
示例可能是url与输入如： url.com?location=gb&color=3&hair=4 ，仍然形成正确的sql查询，如下所示： url.com?location=gb&hair=1 ，并且不会遇到WHERE和AND等等的问题以及语句中的空变量

I'm trying to make a simple filter search where-by you can choose from a series of 3 drop downs and then based upon this the results are then displayed, How would I go about adjusting the sql query for each and if you were to only choose to search from aone of the 3 rather than all 3 etc... example there could be the url with input such as: url.com?location=gb&color=3&hair=4 and still form the correct sql query for something like this: url.com?location=gb&hair=1 and not encounter problems with WHERE and AND etc etc and empty variables in the statement

这不需要是一个巨大的功能检查使用如果看到如何设置所有的可能性的数据？

Would this not need to be a massive function to check using if to see how the data is set for all possibilities?

感谢，
Stefan

Thanks, Stefan

推荐答案

有一天，我觉得和你的很相似：

I answered a question the other day that I think is pretty similar to yours:

PHP：准备语句，IF语句需要帮助

这个想法是你使用条件逻辑在您的代码中根据您的应用程序输入根据需要收集条款。

The idea is that you use conditional logic in your code to collect terms as needed corresponding to your application inputs. Then you join them together in such a way that produces the right SQL expression.

它需要一些应用程序函数来动态构建SQL表达式，还有一些技术可以使用它尽可能简洁。如果你真的有很多可能的搜索项，你可能会得到一个冗长的函数。但猜猜什么？如果你有复杂的输入，你不必惊讶，你需要复杂的代码来处理。

It does need some application function to build the SQL expression dynamically, and there are techniques to make it as concise as possible. If you really have many possible search terms, you might end up with a lengthy function. But guess what? If you have complex inputs, it should be no surprise that you need complex code to deal with them.

：

url.com?location=gb&color=3&hair=4

url.com?location=gb&color=3&hair=4

好的，你有三个输入，你必须从这些动态建立一个SQL查询。让我们从结束开始，向后工作。最终你想要一个这样的SQL表达式：

Okay, you have up to three inputs and you have to dynamically build up an SQL query from these. Let's start from the end and work backwards. Ultimately you want an SQL expression like this:

WHERE (location = 'gb') AND (color = 3) AND (hair = 4)

如果你有一个三个词的数组，你可以使用 implode（） 功能。但你也可能少于三个。您可以处理任何数量的术语，将不少的术语放入数组，并在每个术语之间插入 AND ：

If you have an array of three terms, you can join them together in PHP using the implode() function. But you may also have fewer than three. You can handle any number of terms by putting however many terms you have into an array and imploding them with AND between each term:

$where_array = array(
        "(location = 'gb')",
        "(color = 3)",
        "(hair = 4)"
    );

$where_expr = "WHERE " . implode(" AND ", $where_array);

那么如何使用这些术语创建数组呢？通过为应用程序当前请求中存在的每个输入有条件地写入代码以附加到数组：

So how do you create the array with these terms? By writing code to append to the array conditionally for each input that is present in your app's current request:

$where_array = array();
if (array_key_exists("location", $_GET)) {
    $location = mysql_real_escape_string($_GET["location"]);
    $where_array[] = "(location = '$location')";
}
if (array_key_exists("color", $_GET)) {
    $color = mysql_real_escape_string($_GET["color"]);
    $where_array[] = "(color = '$color')";
}
if (array_key_exists("hair" $_GET)) {
    $hair = mysql_real_escape_string($_GET["hair"]);
    $where_array[] = "(hair = '$hair')";
}

完成所有操作后，您的数组包含零到三个元素。如果它有一个或多个，你想生成一个 WHERE 子句，如前所示，否则跳过它。

After all that's done, your array has between zero and three elements. If it has one or more, you want to generate a WHERE clause as shown previously, otherwise skip it.

$where_expr = '';
if ($where_array) {
    $where_expr = "WHERE " . implode(" AND ", $where_array);
}

然后将 $ where_expr 到您的基线SQL查询。

Then append the $where_expr to your baseline SQL query.

$sql .= $where_expr

$ params 是查询参数的一种替代方法，一个SQL表达式，而不是 mysql_real_escape_string（）。它不是强制性的（事实上PHP的旧的mysql扩展不支持查询参数），但我建议切换到PDO，以便您可以使用此功能。请参见示例： PDO :: prepare（） 。

The stuff about $params is for query parameters, which is an alternative method of including dynamic values into an SQL expression, instead of mysql_real_escape_string(). It's not mandatory (and in fact PHP's old mysql extension doesn't support query parameters) but I recommend switching to PDO so you can use this feature. See example here: PDO::prepare().

这篇关于基于输入在函数中调整sql语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！