I am practising with the AdventureWorks database for now and I will be receiving strings like the following: SalesOrderNumber=SOH123 and CustomerID=1. The strings may not always contain "=", as they may be >, <, >=, <=, !=, <>.

Ideally I would like to split each string into 3 fields - the database column to query, the comparison (e.g. =, >, !=, etc) and the value to search for.

I can achieve what I want with lots of code, comparing character by character, but I am hoping someone can suggest a really simple way to do this, as I am sure this must be a fairly common task for websites.

I don't want to just use the strings as they come through, as I need to sanitise them first. I also don't want to send bad queries to the database that may generate SQL errors.

CodePudding user response：

This is my aproach using foreach, explode and array functions:

$strings = [
    'SalesOrderNumber=SOH123',
    'CustomerID=1',
    'BigOrEqual>=44',
    'SmallOrEqual<=67',
    'NotEqual!=123',
    'NotEqual<>2000',
    'Smaller<21',
    'Bigger>12',
];
function explodeOperators(array $strings) : array
{
    $operators = ['>=','<=','!=','<>','<','>','=']; // do not change this order
    $result = [];
    foreach ($strings as $expression) {
        $found = false;
        foreach ($operators as $operator) {
            $exploded = explode($operator, $expression);
            if (count($exploded) > 1 and $found === false) {
                $found = true;
                array_splice($exploded, 1, 0, $operator);
                $result[] = $exploded;
            }
        }
    }
    return $result;
}
$result = explodeOperators($strings);
print_r($result);

That will result:

Array
(
    [0] => Array
        (
            [0] => SalesOrderNumber
            [1] => =
            [2] => SOH123
        )

    [1] => Array
        (
            [0] => CustomerID
            [1] => =
            [2] => 1
        )

    [2] => Array
        (
            [0] => BigOrEqual
            [1] => >=
            [2] => 44
        )

    [3] => Array
        (
            [0] => SmallOrEqual
            [1] => <=
            [2] => 67
        )

    [4] => Array
        (
            [0] => NotEqual
            [1] => !=
            [2] => 123
        )

    [5] => Array
        (
            [0] => NotEqual
            [1] => <>
            [2] => 2000
        )

    [6] => Array
        (
            [0] => Smaller
            [1] => <
            [2] => 21
        )

    [7] => Array
        (
            [0] => Bigger
            [1] => >
            [2] => 12
        )

)