php - 如何使用 PHP SQL 解析器将数组值转换为 MySQL 查询？

Question

我正在使用 PHP SQL PARSER

我的代码

<?php
    require_once dirname(__FILE__) . '/../src/PHPSQLParser.php';

    $sql = 'SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
    FROM Orders
    LEFT JOIN Customers ON Orders.CustomerID=Customers.CustomerID where 
    Customers.CustomerName = "Siddhu"'; 

    $sql = strtolower($sql);
    echo $sql . "\n";
    $parser = new PHPSQLParser($sql, true);
    echo "<pre>";
    print_r($parser->parsed);
?>

我得到的输出类似于下面的数组

Array (
    [SELECT] => Array
        (
            [0] => Array
                (
                    [expr_type] => colref
                    [alias] => 
                    [base_expr] => orders.orderid
                    [no_quotes] => orders.orderid
                    [sub_tree] => 
                    [delim] => ,
                    [position] => 7
                )

            [1] => Array
                (
                    [expr_type] => colref
                    [alias] => 
                    [base_expr] => customers.customername
                    [no_quotes] => customers.customername
                    [sub_tree] => 
                    [delim] => ,
                    [position] => 23
                )

            [2] => Array
                (
                    [expr_type] => colref
                    [alias] => 
                    [base_expr] => orders.orderdate
                    [no_quotes] => orders.orderdate
                    [sub_tree] => 
                    [delim] => 
                    [position] => 47
                )

        )

    [FROM] => Array
        (
            [0] => Array
                (
                    [expr_type] => table
                    [table] => orders
                    [no_quotes] => orders
                    [alias] => 
                    [join_type] => JOIN
                    [ref_type] => 
                    [ref_clause] => 
                    [base_expr] => orders
                    [sub_tree] => 
                    [position] => 70
                )

            [1] => Array
                (
                    [expr_type] => table
                    [table] => customers
                    [no_quotes] => customers
                    [alias] => 
                    [join_type] => LEFT
                    [ref_type] => ON
                    [ref_clause] => Array
                        (
                            [0] => Array
                                (
                                    [expr_type] => colref
                                    [base_expr] => orders.customerid
                                    [no_quotes] => orders.customerid
                                    [sub_tree] => 
                                    [position] => 101
                                )

                            [1] => Array
                                (
                                    [expr_type] => operator
                                    [base_expr] => =
                                    [sub_tree] => 
                                    [position] => 118
                                )

                            [2] => Array
                                (
                                    [expr_type] => colref
                                    [base_expr] => customers.customerid
                                    [no_quotes] => customers.customerid
                                    [sub_tree] => 
                                    [position] => 119
                                )

                        )

                    [base_expr] => customers on orders.customerid=customers.customerid
                    [sub_tree] => 
                    [position] => 88
                )

        )

    [WHERE] => Array
        (
            [0] => Array
                (
                    [expr_type] => colref
                    [base_expr] => customers.customername
                    [no_quotes] => customers.customername
                    [sub_tree] => 
                    [position] => 146
                )

            [1] => Array
                (
                    [expr_type] => operator
                    [base_expr] => =
                    [sub_tree] => 
                    [position] => 169
                )

            [2] => Array
                (
                    [expr_type] => const
                    [base_expr] => "siddhu"
                    [sub_tree] => 
                    [position] => 171
                )

        )

)

现在我想使用这个数组生成查询。我为什么要这样做，稍后我会向这个数组添加额外的参数。就像我在 WHERE 子句或表中传递附加条件

例如:上一个查询

 $sql = 'SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
    FROM Orders
    LEFT JOIN Customers ON Orders.CustomerID=Customers.CustomerID where 
    Customers.CustomerName = "Siddhu"';

现在我想在 where 子句中再传递两个条件，例如在 WHERE 条件 Customers.CustomerID = "123"和 status = "Active"和created_by = 1 之后；

所以我的最终查询是这样的

  $sql = 'SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
    FROM Orders
    LEFT JOIN Customers ON Orders.CustomerID=Customers.CustomerID where 
    Customers.CustomerName = "Siddhu" AND Customers.CustomerID = "123" and status = "Active" and created_by = 1;

那我该如何实现呢，或者PHPSQLPARSER中有什么功能吗？使用这个数组有任何函数来生成查询吗？感谢您的提前，并对任何语法错误表示歉意

Answer 1

要从数组构建查询，PHPSQLParser有一个creator方法，

来自此处的文档:Parser manual

There are two ways in which you can create statements from parser output

Use the constructor

The constructor simply calls the create() method on the provided parser tree output for convenience.

 $parser = new PHPSQLParser('select 1');

 $creator = new PHPSQLCreator($parser->parsed);

 echo $creator->created;

Use the create() method

 $parser = new PHPSQLParser('select 2');

 $creator = new PHPSQLCreator();

 echo $creator->create($parser->parsed); 

 /* this is okay, the SQL is saved in the _created_ property. */

 /* get the SQL statement for the last parsed statement */

 $save = $creator->created;

当然是从$parser->parsed开始是 array ，你可以传递你自己的数组

echo $creator->create($myArray); 

要向数组添加条件，可以将其添加到 WHERE条件数组

每个条件都有 3 个数组定义 colref (列名)，operator (好吧..运算符)和const (值)

棘手的部分是 position在 WHERE 的子数组中因为您需要指定要将这三个中的每一个插入的确切位置，所以基于 WHERE在您提供的示例中，您可以看到运算符 = 的位置是 169 (从 0 开始)

检查这个工具可以看到character position in a string (从 1 开始)。

基于此Complexe example

您的最终成绩WHERE数组应该如下所示(但我不确定您是否需要 [no_quotes] 键):

[WHERE] => Array
(
    [0] => Array
        (
            [expr_type] => colref
            [base_expr] => customers.customername
            [no_quotes] => customers.customername
            [sub_tree] => 
            [position] => 146
        )

    [1] => Array
        (
            [expr_type] => operator
            [base_expr] => =
            [sub_tree] => 
            [position] => 169
        )

    [2] => Array
        (
            [expr_type] => const
            [base_expr] => "siddhu"
            [sub_tree] => 
            [position] => 171
        )
    
    // adding other conditions 

    [3] => Array
        (
            [expr_type] => operator
            [base_expr] => and
            [sub_tree] => 
            [position] => 180
        )
        
    [4] => Array
        (
            [expr_type] => colref
            [base_expr] => customers.CustomerID 
            [no_quotes] => customers.CustomerID 
            [position] => 184
        )

    [5] => Array
        (
            [expr_type] => operator
            [base_expr] => =
            [sub_tree] => 
            [position] => 205
        )

    [6] => Array
        (
            [expr_type] => const
            [base_expr] => "123"
            [sub_tree] => 
            [position] => 207
        )
        
    [7] => Array
        (
            [expr_type] => operator
            [base_expr] => and
            [sub_tree] => 
            [position] => 213
        )
            
    [8] => Array
        (
            [expr_type] => colref
            [base_expr] => status 
            [no_quotes] => status
            [position] => 217
        )

    [9] => Array
        (
            [expr_type] => operator
            [base_expr] => =
            [sub_tree] => 
            [position] => 224
        )

    [10] => Array
        (
            [expr_type] => const
            [base_expr] => "Active"
            [sub_tree] => 
            [position] => 226
        )
        
    [11] => Array
        (
            [expr_type] => operator
            [base_expr] => and
            [sub_tree] => 
            [position] => 235
        )
            
    [12] => Array
        (
            [expr_type] => colref
            [base_expr] => created_by  
            [no_quotes] => created_by 
            [position] => 239
        )

    [13] => Array
        (
            [expr_type] => operator
            [base_expr] => =
            [sub_tree] => 
            [position] => 250
        )

    [14] => Array
        (
            [expr_type] => const
            [base_expr] => 1
            [sub_tree] => 
            [position] => 252
        )
)

PS:我使用了具有您提供的多个条件的查询，并取消了缩进和换行符来确定位置，如果您没有所需的字符串输出，请使用这些值这应该只是一个例子。

我希望这对您有所帮助，或者至少给您一个想法，祝​​您好运。