使用where子句和if语句进行MySQL查询

Question

I'm stuck again on a MySQL query and I would like some help.

I am trying to find the best possible way to do this. What I want to do is check if $var = 'x' and based on that run a different query.

Part of the code:

if ($var1 == '0' AND $var2 != '0' AND $var3 != '0' AND $var4 != '0') {
    echo $query = "SELECT * FROM table WHERE var1 = ''";
}elseif ($var1 == '1' AND $var2 != '0' AND $var3 != '0' AND $var4 != '0') {
    echo $query = "SELECT * FROM table WHERE var1 = '1'";
}elseif ($var1 != '0' AND $var2 == '0' AND $var3 != '0' AND $var4 != '0') {
    echo $query = "SELECT * FROM table WHERE var2 = ''";
}elseif ($var1 == '0' AND $var2 == '0' AND $var3 != '0' AND $var4 != '0') {
    echo $query = "SELECT * FROM table WHERE var1 = '' AND var2 = ''";
} etc..

The connections are a LOT. Is there a better way to do this? Pretty much each variable (in total 7) got 3 different choices. I can't put var = $var under the WHERE clause because sometimes when $var = '0' I mean all the results so I don't even put it there..

This is a filtering script/code by the way..

Does this even make sense to you? Thanks!

Answer 1

I think this can be done with a single query and varying WHERE conditions. You need to first define when to put var1 = '' in your WHERE clause. You should find out the cases when you need varX = '' in your query one by one.

$conds = array();
if ($var1 == '0' || $var2 == '0') {
  $conds[] = "var1 = ''"; // this should be the only point where var1 is included in your query, so you need to have every possible condition in this if part
}
if ($var3 == '0' && $var4 != '0') {
  $conds[] = "var3 = ''";
}
// ..etc..
$query = "SELECT * FROM TABLE WHERE " . implode(' AND ', $conds);