php 选择 ... 其中 id = 任何值。是否可以？

select ... where id = any value. is it possible?

提问by Simon

look at this table please

请看这张表

table
|id| |name| |order|

i must get the rows, where name = somethingand order = somevalue

我必须得到行，在哪里name = something和 order = somevalue

so i write

所以我写

select `id` from `table` where `name` = 'something' and `order` = 'somevalue'

but depend on php logic, sometimes i need to get all rows, where name = something, independently of ordervalue. i don't want to change the query structure, because in practise there are many number of fields, and possible count of queries will become very big. so i want to savethe structure of query, and when i need to select just by name, i want to write something like this:

但取决于 php 逻辑，有时我需要获取所有行，其中name = something，独立于order值。我不想改变查询结构，因为实际上有很多字段，查询的可能数量会变得非常大。所以我想保存查询的结构，当我只需要按名称选择时，我想写这样的东西：

select `id` from `table` where `name` = 'something' and `order` = any value 

is it possible?

是否可以？

thanks

谢谢

采纳答案by Chad Birch

Well, it's kind of a hack, but if you really need to do this, it'll work like this:

嗯，这有点像黑客，但如果你真的需要这样做，它会像这样工作：

select `id` from `table` where `name` = 'something' and `order` = `order`

Then you're just saying "wherever order is the same as itself", so it's always true.

那么你只是说“任何地方的顺序与它自己相同”，所以它总是正确的。

回答by Michael Madsen

No, this is not possible. You need to change the structure (optionally to a LIKE so you can use '%', but that's very ugly).

不，这是不可能的。您需要更改结构（可以选择更改为 LIKE，以便您可以使用“%”，但这非常难看）。

However, you don't need to write a different query to handle every possible combination. You can simply create the query dynamically:

但是，您无需编写不同的查询来处理每种可能的组合。您可以简单地动态创建查询：

//create base query
$query = "select `id` from `table` where `name` = 'something' ";

//add order if we need it
if ($use_order)
  $query .= "and `order` = 'somevalue' ";

//repeat for any other optional part

Note that you should of course still take proper measures to avoid SQL injection and other security issues - I have not included this here in order to keep things simple.

请注意，您当然仍然应该采取适当的措施来避免 SQL 注入和其他安全问题——为了简单起见，我没有将其包含在此处。

回答by Quassnoi

If you are using bound parameters, it would be impossible.

如果您使用绑定参数，那将是不可能的。

If you just substitute the values, you can do the following:

如果您只是替换这些值，您可以执行以下操作：

select `id` from `table` where `name` = 'something' and `order` = `order`

回答by Platinum Azure

I don't think you have any choice... Once you do a selection you can't "unfilter" and get more rows.

我不认为你有任何选择......一旦你做了一个选择，你就不能“取消过滤”并获得更多行。

You should just use two queries-- either two independent queries, or one that selects on the name into a temp table, and then (optionally) one that further selects on the order attribute.

您应该只使用两个查询——或者两个独立的查询，或者一个将名称选择到临时表中，然后（可选）一个进一步选择 order 属性。

回答by Brock Weaver

Like Chad said above, just set the column equal to itself. But be careful, on some platforms / install configurations, NULL != NULL:

就像 Chad 上面说的那样，只需将列设置为等于自身。但要小心，在某些平台/安装配置上，NULL != NULL：

select `id` from `table` where `name` = 'something' and coalesce(`order`,'') = coalesce(`order`,'')

回答by Geoff Adams

This is a common theme with database queries - you need a variable query depending on how much filtering you wish to apply to the data it queries. You could go the route of having your query repeated as a string throughout your code, but that is bad practice as it increases the complexity of the code needlessly. Chances for errors occur if you need to change the query for some reason, and have to change it in multiple places as a result.

这是数据库查询的一个常见主题 - 您需要一个变量查询，具体取决于您希望对其查询的数据应用多少过滤。您可以走在整个代码中将查询作为字符串重复的路线，但这是不好的做法，因为它不必要地增加了代码的复杂性。如果出于某种原因需要更改查询，并且因此必须在多个位置更改它，则可能会发生错误。

The better solution is to create a function which builds the query for you execute:

更好的解决方案是创建一个函数来为您执行构建查询：

function buildMyQuery($name, $order = null) {
    $sql = "SELECT `id` FROM `table` WHERE `name`='$name'";

    if ($order != null) {
        $sql .= " AND `order`='$order'";
    }

    return $sql;
}

You could then run this for just using the 'name' field:

然后，您可以仅使用“名称”字段来运行它：

$query = buildMyQuery("somename");

Or this for using both fields:

或者这用于使用两个字段：

$query = buildMyQuery("somename", "someorder");

As someone mentioned above, this code is deliberately simplified and contains no contingency for possibly dangerous data passed in via $name or $order. You would need to use mysql_real_escape_string or something similar to clean the data first, at the beginning of the function before either piece of data is used.

正如上面有人提到的，这段代码是故意简化的，并且不包含通过 $name 或 $order 传入的可能危险数据的意外情况。在使用任一数据之前，您需要在函数的开头使用 mysql_real_escape_string 或类似的东西来首先清理数据。

Dynamic query generation is a fact of life as Byron says, so I would become accustomed to it now rather than using hack-ish workarounds.

正如拜伦所说，动态查询生成是生活中的事实，所以我现在会习惯它而不是使用黑客式的解决方法。

回答by Relequestual

On reflection, I have a better answer. My colleague showed me a way this can be done.

经过反思，我有一个更好的答案。我的同事向我展示了一种可以做到这一点的方法。

My example...

我的例子...

Select rentals.* From rentals Where ((? = '') OR (user_id = ?))

The variables must be the same.

变量必须相同。

If they are both 5 for example, the first boolean will be false, but the second will be true, for the rows where the users id is 5.

例如，如果它们都是 5，对于用户 ID 为 5 的行，第一个布尔值将为 false，但第二个布尔值将为 true。

If you require "all", setting as an empty string will result in all rows being seen to meet the where clause condition.

如果需要“all”，则设置为空字符串将导致看到所有行都满足 where 子句条件。

回答by Steve Bauman

Can't you just use a not nullquery here?

你不能not null在这里只使用查询吗？

select `id` from `table` where `name` = 'something' and `order` is not null;

回答by Dan W.

You should be able to do it like this:

你应该可以这样做：

select `id` from `table` where `name` <>'' and `order` <>''

That will select anywhere that the value is not equal to blank.

这将选择值不等于空白的任何地方。

回答by 1138

$sql = "SELECT * FROM auctions WHERE id = id ";

if ($category !== "ANY") { 
$sql .= "AND category = $category "; }

if ($subcategory !== "ANY") { 
$sql .= "AND subcategory = $subcategory "; }

if ($country !== "ANY") { 
$sql .= "AND country = $country "; }



$sql .=  "ORDER BY $order $sort LIMIT $limit OFFSET $offset";