php - 将自定义查询转换为 SQL

Question

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

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

我尝试过使用 preg_replace 或 explode 来循环执行 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

因为您不需要嵌套表达式。这很简单，正则表达式实际上只是懒惰:

$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 子句。