Converting custom queries into SQL

Question

What would be the best way of converting the following input into SQL using PHP?

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

I've had various stabs using preg_replace or explode to loop through and do if statements but haven't got anything to work consistently.

I basically need to turn something like

+a +(c/d/e)

Into

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

Thanks!

UPDATE:

Here's my attempt at it. I'm sure there is an easier way...

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

Since you do not need to nest the expression. It's simple and the regex is actually just laziness:

$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)";
}

It would return the statement starting with " AND", which could be adapted, but it's easier to cheat and just prepend "TRUE" to turn it into a valid WHERE clause.