mysql - if value = 0 shorten where statement

Question

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:

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

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

$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.

One is made in php:

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

Second is made in 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?

Answer 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?

Answer 2

You could so something like this on the SQL side:

"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.