使用PHP我試圖拆分一個字串來為SQL搜索構建一個where子句

我現在正在練習 AdventureWorks 資料庫，我將收到如下字串：SalesOrderNumber=SOH123 和 CustomerID=1。字串可能并不總是包含“=”，因為它們可能是 >、<、>=、<=、!=、<>。

理想情況下，我想將每個字串拆分為 3 個欄位 - 要查詢的資料庫列、比較（例如 =、>、!= 等）和要搜索的值。

我可以用大量代碼實作我想要的東西，逐個字符地比較，但我希望有人能提出一種非常簡單的方法來做到這一點，因為我相信這對于網站來說一定是一項相當常見的任務。

我不想只在字串通過時使用它們，因為我需要先對它們進行消毒。我也不想向可能產生 SQL 錯誤的資料庫發送錯誤的查詢。

uj5u.com熱心網友回復：

您可以preg_split()為此使用，并設定 PREG_SPLIT_DELIM_CAPTURE 標志，它還會記住拆分的分隔符

$result = preg_split('/([>, <, >=, <=, !=, <>])/', $string, 0, PREG_SPLIT_DELIM_CAPTURE );

例子

$strings = ['SalesOrderNumber=SOH123', 
            'CustomerID=1',
            'SalesOrderNumber>SOH123',
            'SalesOrderNumber<SOH123',

            ];
foreach ($strings as $key => $string){
    $result = preg_split('/([>, <, >=, <=, !=, <>])/', $string, 0, PREG_SPLIT_DELIM_CAPTURE );
    print_r($result);
}

結果

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

uj5u.com熱心網友回復：

這是我使用foreach、expand和array函式的方法：

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

這將導致：

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
        )

)

標籤：php sql-server

上一篇：phpMailer檔案附件不起作用，有什么建議嗎？

下一篇：如何使用javascript在php的輸入框中生成隨機值