mysql - 如果value = 0则缩短where语句

I wonder if it's possible to shorten query depending on some variable value in elegant way.

我想知道是否有可能以优雅的方式根据某些变量值缩短查询。

For example: I have value named $var = 0 and I would like to send a query that looks like this:

例如:我有一个名为$ var = 0的值,我想发送一个如下所示的查询:

$query = "SELECT id, name, quantity FROM products WHERE quantity > 100";

But whan the $var != 1 I'd like to send a query like this:

但是$ var!= 1我想发送一个这样的查询:

$query = "SELECT id, name, quantity FROM products WHERE quantity > 100 AND id = '$var'";

So depending on value of $var I want to execute one of queries. They differ only with last expression. I found two possible solutions but they are not elegant and I dont like them at all.

因此,根据$ var的值,我想执行一个查询。它们仅与最后一个表达不同。我发现了两种可能的解决方案,但它们并不优雅,我根本不喜欢它们。

One is made in php:

一个是用PHP制作的:

if ( $var == 0 ) {
  $query_without_second_expression
} else {
  $query_with_second_expression
}

Second is made in mysql:

第二个是在mysql中制作的:

SELECT WHEN '$var' <> 0 THEN id, name, quantity 
FROM products WHERE quantity > 100 AND id = '$var' ELSE id, name, quantity 
FROM products WHERE quantity > 100 END

but i dont like it - each idea doubles queries in some whay. Can I do something like this?

但我不喜欢它 - 每个想法都会在某些问题中加倍查询。我可以这样做吗?

SELECT id, name, quantity 
FROM products WHERE quantity > 100 
CASE WHEN $var <> 0 THEN AND id = '$var'

It's much shorter, and adds part of query if needed. Of course real query is much more complicated and shorter statement would be really expected. Anyone has an idea?

它更短,并在需要时添加部分查询。当然,真正的查询要复杂得多,并且真正期望更短的语句。有人有想法吗?

9 个解决方案

#1

If I understand well..

如果我理解得好..

$query = "SELECT id, name, quantity FROM products WHERE quantity > 100";
if ( $var != 0 ) {
  $query .= " AND id = '$var'";
}

do you like it?

你喜欢它吗?

#2

You could so something like this on the SQL side:

你可以在SQL端这样的事情:

"SELECT id, name, quantity FROM products WHERE quantity > 100 AND (id = '$var' OR '$var' = 0)

But performance could be impacted. I would suggest building the appropriate query on the PHP side.

但性能可能会受到影响。我建议在PHP端构建适当的查询。

#3

I'm no PHP developer (it is PHP, right?), but wouldn't it be easiest to build your query from a concatenated string?

我不是PHP开发人员(它是PHP,对吧?),但从串联字符串构建查询是不是最简单?

Pseudo-code:

$my_query = "SELECT id, name, quantity FROM products WHERE quantity > 100"

if ($var != 1)
   $my_query = $my_query + " AND id = '$var'";
end if;

/*go ahead with your query*/

#4

You can do this:

你可以这样做:

SELECT id, name, quantity 
FROM products 
WHERE  1 = 1
       AND ( $q   IS NULL OR quantity > $q)
       AND ( $var IS NULL OR id = $var)

If you want only the first condition to run then pass $q = 100 and $var = NULL, therefore the second condition will be ignored. And for the second query pass the $q = 100 and $var = id value and you will got the your second query.

如果只想运行第一个条件,则传递$ q = 100和$ var = NULL,因此将忽略第二个条件。并且对于第二个查询传递$ q = 100和$ var = id值,您将获得第二个查询。

#5

If they only differ in additional where-statements, I would probably still stay in PHP and do the following:

如果他们只在其他where语句中有所不同,我可能会继续使用PHP并执行以下操作:

$conditions = array();
$conditions[] = "(quantity > 100)"

if ($var == 0)
  $conditions[] = "(id = '$var')";

if (some-other-expression)
  $conditions[] = "(myfield = 'foo' OR myfield = 'bar')";

$sql = "
  SElECT id, name, quantity
  FROM products
  WHERE
";
$sql .= $conditions.join(" AND ");

/ Carsten

#6

I would use a ternary for this:

我会用这个三元组:

$query = "SELECT id, name, quantity FROM products WHERE quantity > 100" . ( $var != 0 ? " AND id = '$var'" : '' );

#7

I'm an Oracle guy, but could you use the IF() function in your constraints, e.g.:

我是Oracle的人,但你可以在约束中使用IF()函数,例如:

SELECT id, name, quantity
  FROM products
 WHERE quantity > 100
   AND id = IF('$var'=0,'%','$var');

The '%' is a wildcard that would match anything, effectively ignoring the 2nd expression.

'%'是一个匹配任何东西的通配符,实际上忽略了第二个表达式。

#8

So if you are using the

所以,如果你正在使用

If or the CASE

如果或CASE

Why don't you use them in your sql query. It would be some like

为什么不在sql查询中使用它们。有点像

if ( $var == 0 ) {
$query_without_second_expression
}
else 
{
 $query_with_second_expression
}

But in you sql query.

但是在你的SQL查询中。

DECLARE @var int
if(@var=1)
BEGIN
    query 1
END

else 
BEGIN
     query 2
end

I guess this will solve your problem. But as a personal advice try to make one query. Even with the variable. We don't believe in changing standard query depending on the conditions.

我想这会解决你的问题。但作为个人建议尝试进行一个查询。即使有变量。我们不相信根据条件更改标准查询。

Still your wish and your desire

仍然是你的愿望和你的愿望

Cheers!!

#9

For this particular problem, go with Paper-bat's simple condition append snippet.

对于这个特殊问题,请使用Paper-bat的简单条件追加代码段。

However, if you have entirely different where statements, consider appending the appropriate where statement to the query once you know what you want. For instance,

但是,如果您具有完全不同的where语句,请考虑在知道所需内容后将相应的where语句附加到查询中。例如,

$query = "SELECT id, name, quantity FROM products ";
if ( $var == 0 ) {
  $query .= "quantity > 100";
} elseif {
  $query .= "quantity > 120 AND id = '$var'";
} elseif {
...
}
...

It all depends on your needs, but neither this or Paper-bat's solutions duplicate query code.

这一切都取决于您的需求,但这个或Paper-bat的解决方案都不会重复查询代码。

#1