php MySQL where 子句等于任何东西 (SELECT * WHERE col = ANY_VALUE)

MySQL where clause equals anything (SELECT * WHERE col = ANY_VALUE)

提问by gerdemb

I'd like to create a query in MySQL that has an optional value. When the value is specified the query is filtered by that value, when the value is not all rows are returned. Here's the idea:

我想在 MySQL 中创建一个具有可选值的查询。当指定值时，查询将按该值过滤，当值不是所有行时都返回。这是想法：

public function doQuery($item = 'ANY_VALUE') {
  $query = "SELECT * FROM table WHERE item = ?";
  db->fetchAll($query,array($item))
  ...
}

doQuery(); // Returns everything
doQuery($item='item1'); // Returns only rows where item = 'item1'

Is there an easy way to do this without creating two query strings depending on the value of $item?

是否有一种简单的方法可以在不根据 $item 的值创建两个查询字符串的情况下执行此操作？

采纳答案by Pekka

As far as I know, no such "any" placeholder exists.

据我所知，不存在这样的“任何”占位符。

If you can use LIKE, you could do

如果你可以使用 LIKE，你可以这样做

SELECT * FROM table WHERE item LIKE '%'

if you can append a condition, you could nullify the itemclause like this:

如果可以附加条件，则可以item像这样取消子句：

SELECT * FROM table WHERE item = ? OR 1=1

(won't work in your example though, because you are passing "item" as a parameter)

（不过在您的示例中不起作用，因为您将“item”作为参数传递）

That's all the options I can see - it's probably easiest to work with two queries, removing the WHEREclause altogether in the second one.

这就是我能看到的所有选项 - 处理两个查询可能最容易，WHERE在第二个查询中完全删除子句。

This would probably work, but I*m not sure whether it's a good idea from a database point of view.

这可能会奏效，但我不确定从数据库的角度来看这是否是一个好主意。

public function doQuery($item = 'ANY_VALUE') {
  $query = "SELECT * FROM table WHERE item = ? OR 1 = ?";
  db->fetchAll($query,array($item, ($item == 'ANY_VALUE' ? 1 : 0))
  ...
}

回答by chanaka777

Better way to do this is first generate sql query from the parameter you need to bother on, and then execute.

更好的方法是首先从您需要打扰的参数生成 sql 查询，然后执行。

function doQuery($params) {
    $query = 'SELECT * FROM mytable ';
    if (is_array($params) // or whatever your condition ) { 
        $query .= 'WHERE item = ' . $params[0];
    }
    $query .= ' ;';

    // execute generated query
    execute($query);
}

回答by Max

You cannot get distinct results without giving distinct query strings.

如果不提供不同的查询字符串，就无法获得不同的结果。

Using $q = "... WHERE item = '$item'"you DO create distinct query strings depending on the value of $item, so it is not that different from using $q = "..." . ($item=='ANY_VALUE' ? something : s_th_else);.

使用$q = "... WHERE item = '$item'"您确实会根据 $item 的值创建不同的查询字符串，因此它与使用 $q = "..." . ($item=='ANY_VALUE' ? something : s_th_else);.

That said I see two or three options:

也就是说，我看到了两三个选项：

• use function doQuery($item = "%") { $query = "SELECT ... WHERE item LIKE '$item'"; ...}
  But then callers to that function must know that they must escape a '%' or '_' character properly if they want to search for an item having this character literally (e.g. for item = "5% alcoholic solution", giving this as argument would also find "50-50 sunflower and olive oil non alcoholic solution".
• use function doQuery($item = NULL) { $query = "SELECT ..."; if ($item !== NULL) $query .= " WHERE item = '$item' "; ...}(where I use NULL to allow any other string or numerical value as a valid "non-empty" argument; in case you also want to allow to search for NULL (without quotes) you must choose another "impossible" default value, e.g., [], and you must anyway use a distinct query without the single quotes which however are very important in the general case), or even:
• use function doQuery($item = NULL) { if($item === NULL) $query = "SELECT ..."; else $query = "SELECT ... WHERE item = '$item' "; ...}, which is more to type but probably faster since it will avoid an additional string manipulation (concatenation of the first and second part).

• 使用function doQuery($item = "%") { $query = "SELECT ... WHERE item LIKE '$item'"; ...}
  但是，该函数的调用者必须知道，如果他们想搜索字面上具有此字符的项目（例如，对于 item =“5% 酒精溶液”，将其作为争论也会找到“50-50 向日葵和橄榄油非酒精溶液”。
• 使用function doQuery($item = NULL) { $query = "SELECT ..."; if ($item !== NULL) $query .= " WHERE item = '$item' "; ...}（我使用 NULL 来允许任何其他字符串或数值作为有效的“非空”参数；如果您还想允许搜索 NULL（不带引号），您必须选择另一个“不可能”的默认值，例如, [], 并且无论如何您都必须使用不带单引号的不同查询，但在一般情况下这非常重要），甚至：
• use function doQuery($item = NULL) { if($item === NULL) $query = "SELECT ..."; else $query = "SELECT ... WHERE item = '$item' "; ...}，键入更多但可能更快，因为它将避免额外的字符串操作（第一部分和第二部分的连接）。

I think the 2nd & 3rd options are better than the first one. You should explain why you want to avoid these better solutions.

我认为第二和第三个选项比第一个更好。您应该解释为什么要避免使用这些更好的解决方案。

PS: always take care of not forgetting the quotes in the SQL, and even to properly escape any special characters (quotes, ...) in arguments which can depend on user input, as to avoid SQL injections. You may be keen on finding shortest possible solutions (as I am), but neglecting such aspects is a no-no: it's not a valid solution, so it's notthe shortest solution!

PS：始终注意不要忘记 SQL 中的引号，甚至正确转义参数中可能依赖于用户输入的任何特殊字符（引号，...），以避免SQL 注入。您可能热衷于寻找最短的解决方案（就像我一样），但忽略这些方面是禁忌：这不是一个有效的解决方案，所以它不是最短的解决方案！

回答by mwittrock

There's really no reason to do this with one query. Running the query with no WHERE clause makes it explicit that you want to return all rows. Running it with a WHERE clause makes it explicit that you want to filter the rows.

真的没有理由用一个查询来做到这一点。运行不带 WHERE 子句的查询明确表示您要返回所有行。使用 WHERE 子句运行它可以明确表示您要过滤行。