动态创建的 WHERE 子句 PHP/MySQL

我正在动态生成 MySQL 查询的 WHERE 部分.到目前为止，我下面的代码运行良好

I am dynamically generating the WHERE portion of a MySQL query. My code below works perfectly so far

请注意: _GET 字符串在我的代码中都是经过验证的 elswhere，但为了将此代码保持在合理的长度，我将它们直接放在下面.对于任何想要做类似于我正在做的事情并在此处使用我的代码作为基础的人，请务必验证您的字符串以避免 mysql 注入.

PLEASE NOTE: _GET strings are all validated elswhere in my code but in order to keep this code down to a reasonable length for this question I have put them in directly below. For anyone looking to do something similar to what I am doing and is using my code here as a base, please be sure to validate your strings to avoid mysql injections.

  /* Loop through each column in the table */

        for ( $i=0 ; $i<count($aColumns) ; $i++ )
        {
             /* check if the column has been marked as searchable and that the param sent from the client contains data */
            if ( $_GET['bSearchable_'.$i] == "true" && $_GET['sSearch_'.$i] != '' )
            {
                if ( $sWhere == "" )
                {
                    $sWhere = "WHERE ";
                }
                else
                {
                    $sWhere .= " AND ";
                }



/* RANGE FILTER CODE - This part is not important to this question but included for completenes */

                $columnFilterValue = mysql_real_escape_string($_GET['sSearch_' . $i]);
                // check for values range
                $rangeSeparator = "~";
                if (!empty($rangeSeparator) && strstr($columnFilterValue, $rangeSeparator)) {
                    // get min and max

                     $columnFilterRangeMatches =  explode('~', $columnFilterValue);

                    // get filter
                    if (empty($columnFilterRangeMatches[0]) && empty($columnFilterRangeMatches[1]))
                        $sWhere .= " 0 = 0 ";
                    else if (!empty($columnFilterRangeMatches[0]) && !empty($columnFilterRangeMatches[1]))
                        $sWhere .= $aColumns[$i] . " BETWEEN '" . $columnFilterRangeMatches[0] . "' and '" . $columnFilterRangeMatches[1] . "' ";
                    else if (empty($columnFilterRangeMatches[0]) && !empty($columnFilterRangeMatches[1]))
                        $sWhere .= $aColumns[$i] . " < '" . $columnFilterRangeMatches[1] . "' ";
                    else if (!empty($columnFilterRangeMatches[0]) && empty($columnFilterRangeMatches[1]))
                        $sWhere .= $aColumns[$i] . " > '" . $columnFilterRangeMatches[0] . "' ";
                } else {



    /* Begin building WHERE clause */


        $sWhere = "WHERE (";

                $aORs = array();

                    for ( $i=0 ; $i<count($aColumns) ; $i++ )
                            {
                                if ( $_GET['bSearchable_'.$i] == "true" && $_GET['sSearch_'.$i] != '' )
                                        {
                                                    $value = $_GET['sSearch_'.$i];

                                                        array_push($aORs, $aColumns[$i]." IN ($value)");


                                        }
                            }
                $sWhere .= implode(" OR ",$aORs);   

        $sWhere .= ')';
                }
            }
        }

现在这段代码做了什么，它需要从客户端发送的逗号分隔值的字符串，并基于这些构建 WHERE 类.

Now what this code does it it takes strings of comma seperated values sent from the client and builds the WHERE clase based on those.

• $aColumns 是一个包含表中列的数组

示例:-

如果参数...

• sSearch_1 包含值 1,3,5,6
• sSearch_2 包含 1,2,3 的值
• sSearch_4 包含的值为 4,5,6
• sSearch 6 包含 7,8,9 的值

然后此代码将生成以下 WHERE 子句:

Then the following WHERE clause would be generated by this code:

 WHERE genre_id IN (1,3,5,6) OR instruments IN (1,2,3) OR emotions IN (4,5,6) OR ratings IN (7,8,9)

这很好用，但是我想通过发送另一个包含正确顺序的 OR 和 AND 列表的字符串来使 OR 或 AND 动态化.

This works fine however I want to make the OR's or AND's dynamic also by sending another string containing a list of OR's and AND's in the correct sequential order.

例如，如果 $_GET['filtertype'] = 像这样的字符串:-

so for instance if $_GET['filtertype'] = a string like this:-

OR,OR,AND

然后它应该返回而不是上面的:

then instead of the above it should return:

WHERE genre_id IN (1,3,5,6) OR instruments IN (1,2,3) OR emotions IN (4,5,6) OR ratings IN (7,8,9)

正如您在上面的代码中看到的，我目前正在通过 implode 函数将 OR 注入到我的数组中.(相关部分代码如下重复)

As you can see in my code above I am currently injecting the OR's in to my array via the implode function. (The relevant part of code is repeated below)

  $sWhere = "WHERE (";

                $aORs = array();

                    for ( $i=0 ; $i<count($aColumns) ; $i++ )
                            {
                                if ( $_GET['bSearchable_'.$i] == "true" && $_GET['sSearch_'.$i] != '' )
                                        {
                                                    $value = $_GET['sSearch_'.$i];

                                                        array_push($aORs, $aColumns[$i]." IN ($value)");


                                        }
                            }
                $sWhere .= implode(" OR ",$aORs); 

        $sWhere .= ')';

如何修改它以根据正确的循环顺序添加正确的 AND 或 OR?

How can I modify this to sequrntially add the correct AND or OR based on the correct loop?