Question

使用PHP将以下输入转换为SQL的最佳方式是什么？

+a - includes a
+(cd) - includes c or d but not both
+(c/d/e) - includes any of these
-f - does not include f

我曾使用preg_replace或爆炸来循环执行和do-if语句，但没有任何东西能始终如一地工作。

我基本上需要像

+a +(c/d/e)

进入

SELECT * FROM the_table 
WHERE description LIKE "%a%" 
AND (description LIKE "%c%" OR description like "%d%" OR description LIKE "%$e%")

谢谢

更新：

这是我的尝试。我相信有一个更简单的方法。。。

public function custom_query($query){

    $fields = " feed_items.description ";

    $return_query = "";

    $query = str_replace(" ",   , $query);

    $pluses = explode( + ,$query);

    foreach($pluses as $plus){

            $plus = trim($plus);

            if (substr($plus,0,1) ==  ( ){
                $return_query .=  AND ( ;

                $plus = str_replace(array( ( , ) ),   , $plus);

                $ors = explode( / , $plus);

                foreach($ors as $or){
                    if ($or){
                        $return_query .= $fields." LIKE  %".$or."%  OR";
                    }
                }

                $return_query = rtrim($return_query,   OR );
                $return_query .=   ) ;
            } else {
                if ($plus){
                    $return_query .=   AND   . $fields.  LIKE  . "% .$plus. %" ;
                }
            }

        }

    $negatives = explode( - ,$query);

    foreach($negatives as $negative){

            $negative = trim($negative);

            if (substr($negative,0,1) ==  ( ){
                $return_query .=  AND ( ;

                $negative = str_replace(array( ( , ) ),   , $negative);

                $ors = explode( \ , $negative);

                foreach($ors as $or){
                    if ($or){
                        $return_query .= $fields." NOT LIKE  %".$or."%  OR";
                    }
                }

                $return_query = rtrim($return_query,   OR );
                $return_query .=   ) ;
            } else {
                if ($negative){
                    $return_query .=   AND   . $fields.  NOT LIKE  . "% .$negative. %" ;
                }
            }

        }

    $return_query =   AND  .ltrim($return_query,   AND  );

    return $return_query;

}

Answer 1

因为你不需要嵌套表达式。它很简单，regex实际上只是懒惰：

$sql = preg_replace_callback(" 
           ([+-])            # AND or NOT
           (w+              # capture a single word
           | (              # or something enclosed in literal braces
                (w+         # word
                  ([/\\])?    # logical delimiter
                [^)]*)       # remainder words and/or delimiters
             )
           ) x",
       "to_sql", $search_pattern);

function to_sql($match) {
    @list($uu, $logical, $word, $words, $or) = $match;

    if ($logical == "+") {
        $sql = " AND (";
    }
    else {
        $sql = " OR (";
    }

    if ($words) {
        $words = explode($or, $words);
    }
    else {
        $words = array($word);
        $or = "/";
    }

    foreach ($words as $i=>$word) {
        $words[$i] = "description LIKE  %$word% ";
    }

    $or = ($or == "/") ? " OR " : " XOR ";
    $sql .= implode($or, $words);

    return "$sql)";
}

它将返回以“AND”开头的语句，这是可以调整的，但更容易作弊，只需在前面加上“TRUE”即可将其转换为有效的WHERE子句。

Answer 2

$pattern = "+a +(c/d/e)";

// replace a, c, d, e with "description like "%something%"
$pattern = preg_replace( #[^()+\-\\/s]# ,  description like "%$0%" , $pattern);

// replace operators
$replacements = array(
     +   =>   and  ,
     -   =>   and not  ,
     \  =>   xor  ,
     /   =>   or  ,
);
$pattern = str_replace(array_keys($replacements), $replacements, $pattern);
$pattern = trim($pattern);

// remove first "and"
// * "and something and something_else" becomes "something and something_else"
// * "and not something and something_else" becomes "not something and something else"
$pattern = preg_replace( #^and # ,   , $pattern);

Patten应该以+、-开头，或者不使用任何运算符。

不确定是否将替换为xor-取决于您希望如何处理（a c）、[cd]等。

友情链接