Alex Rivera | Logout

mysql - if value = 0 shorten where statement

Asked 2012-04-10T14:18:52.713
8

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?

Edit
Report

1 Answer

1

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

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.

answered 2012-04-10T14:30:05.297

Your Answer