使用“AND”构建动态SQL表达式没有混淆嵌套条件？ [英] Build dynamic SQL with "AND" expressions without confusing nested conditionals?

问题描述

我对php和编码很新。

I'm fairly new to php and coding in general.

我有一系列条件我需要测试它们是否已设置。
他们是$ option1，$ option2，$ option3

I have a series of conditions I need to test if they are set. They are $option1, $option2, $option3

if (isset($option1)){
    if (isset($option2)){
        if (isset($option3)){
            $query = "SELECT *
                               FROM Group
                               WHERE FirstOption = '$option1' AND SecondOption = '$option2' AND ThirdOption = '$option3'";
        }
        else {
            $query = "SELECT *
                               FROM Group
                               WHERE FirstOption = '$option1' AND SecondOption = '$option2";
        }
    }
    else {
        $query = "SELECT *
                           FROM Group
                           WHERE FirstOption = '$option1' AND ThirdOption = '$option3";
    }
}
else {
    // .. snip, much more duplicated code ..
}

所以这里的问题是我需要根据条件是否设置对mysql进行唯一查询。但是它们在彼此内部很多，我实际上有7个选项，所以分支绝对是巨大的！我几乎让自己感到困惑，于3点停了下来。

So the issue here is I need unique queries for mysql based upon whether the conditions are set or not. But they are within each other a great deal and I actually have 7 lots of options, so the branching is absolutely massive! I stopped at three after nearly confusing myself.

必须有一个更优秀的方式来写这个 - 有人能帮助我更好地理解吗？

There has to be a far superior way of writing this - could anybody help me understand better?

推荐答案

不需要动态的sql构建。只需使用逻辑。

No dynamic sql building needed. Just use logic.

SELECT *
  FROM Group
 WHERE (FirstOption = '$option1' or '$option1' = '') 
   AND (SecondOption = '$option2' or '$option2' = '')
   AND (ThirdOption = '$option3' or '$option3' = '')

理想情况下，您会使用占位符和预备语句，但上述内容最为明确，仅供教学之用。

Ideally you would use placeholders and prepared statements, but the above is most clear for instructional purposes.

如果有人好奇 - 这对此没有重大的查询开销。任何现代数据库引擎都会将'$ option1'=''表达式优化为常量值，仅将其评估为布尔值一次。

In case anyone is curious - there's no significant query overhead to this. Any modern database engine will optimize the '$option1' = '' expression into a constant value, evaluating it to boolean only once.

这篇关于使用“AND”构建动态SQL表达式没有混淆嵌套条件？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！