Split strings in array to form three-element subarrays

Question

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.

Perhaps you can just pass through a JSON array of {"ColumnName":" SalesOrderNumber","Op":"=","Value":"SOH123"} etc — Charlieface
– Charlieface, Commented Dec 13, 2021 at 14:28
That would be the easy way, but it would require changes to the front end which someone else is managing. I am managing the back-end processes. If I was developing both front and back end then this would be my preferred choice. — ted_snik
– ted_snik, Commented Dec 14, 2021 at 15:04

mickmackusa · Accepted Answer · 2022-07-29 07:35:04Z

It is indirect and feasibly inefficient to make calls of explode() within nested loops with break condition.

I recommend preg_split() as the right tool for the job. Create a capture group containing a character class of whitelisted symbols. Allow a maximum of 3 elements as its return value. Use the PREG_SPLIT_DELIM_CAPTURE flag to inform the function to preserve the delimiting comparison operators when exploding the string into three parts.

Code: (Demo)

var_export(
    array_map(
        fn($v) => preg_split(
            '/([!<=>]+)/',
            $v,
            3,
            PREG_SPLIT_DELIM_CAPTURE
        ),
        $array
    )
);

Output:

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',
  ),
)

masterguru · Accepted Answer · 2021-12-13 12:09:41Z

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
        )

)

Thanks, you were correct that the first solution did not work in all cases. However, your solution does work.

Collectives™ on Stack Overflow

Split strings in array to form three-element subarrays

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related