menu

Questions & Answers

Parse logical text string and make SQL where condition in PHP

I got into a requirement to parse this type of input string to a where condition in laravel eloquent query

Input string:

apple or (orange and lime) or (cherry and "green apple")

Desired output:

where fruit = 'apple' 
or ( fruit = 'orange' and fruit = 'lime')
or (fruit = 'cherry' and fruit = 'green apple')

I tried regex but didn't work out well.

It would be nice if some one can suggest to get this in array grouping, so that it can be reprocessed to query string

EDIT: SOLVED

$string = 'apple or (orange and lime) or (cherry and "green apple")';

$parts = preg_split('/("[^"]+"|\(|\)|\s+)/', $string, -1, PREG_SPLIT_DELIM_CAPTURE | PREG_SPLIT_NO_EMPTY);
$sub_parts = array();
foreach ($parts as $part) {
    if (preg_match('/\(|\)/', $part)) {
        $sub_parts[] = $part;
    } elseif (preg_match('/^"[^"]+"$/', $part)) {
        $sub_parts[] = $part;
    } else {
        $sub_parts = array_merge($sub_parts, preg_split('/\s+/', $part, -1, PREG_SPLIT_NO_EMPTY));
    }
}
//print_r($sub_parts);


$query = "WHERE ";
$word = '';
foreach ($sub_parts as $part) {
    if($part == 'and' || $part == 'or' || $part == '(' || $part == ')') {
        $query .= $part." ";
    } else {
        $query .= "fruit = '".str_replace('"', '', $part)."' ";
    }
}

echo $query;

Output

WHERE fruit = 'apple' or ( fruit = 'orange' and fruit = 'lime' ) or ( fruit = 'cherry' and fruit = 'green apple' )
Comments:
2023-01-17 00:48:01
Every time you meet possibly escaped quotes and possibly nested parenthesis, regexp-only parser gives up. You need lexer first of all. And a parser after that. Also Shunting yard algorithm might be helpfull here.
2023-01-17 00:48:01
The reason for what @Jared points out here is a general, principle one: regular expressions as a tool are simply not powerful enough to process such input data. That is a direct statement that follows from theoretical computer science, from the field of artificial language theory. The structure of your input data is context sensitive, yet regular expressions can only process context free languages.
2023-01-17 00:48:01
I'm not sure how much regex experience you have, but I found regex101.com a nice tool to rest regex with
2023-01-17 00:48:01
It is never appropriate to edit a question to include the "solution". This question does not have a minimal reproducible example so we cannot possibly reopen it.
Answers(0) :