如何通过函数从数组数据格式化SQL字符串

I am trying to create a sort of dynamic function that will return a formatted string that will be added to an SQL query. The function will take an array of data, and based on the data, it will format it into a string that will be added to the SQL query, the string will include the WHERE, ORDER BY, and LIMIT of the query.

At the moment i have a working set of if statements, which takes the data in the array and creates the string. This is the output:

WHERE status != 2 AND category_id = 3 ORDER BY views ASC , date DESC

That's how its supposed to show in the end (i didn't add the limit), but i want to create a function that will do it all dynamically instead of all of the if statements for each array type.

At the moment, i am working with an array like this:

Array
(
    [where] => Array
        (
            [status] => Array
                (
                    [op] => !=
                    [value] => 2
                )

            [category_id] => Array
                (
                    [op] => =
                    [value] => 3
                )

        )

    [orderBy] => Array
        (
            [views] => ASC
            [date] => DESC
        )

    [offsetLimit] => Array
        (
            [offset] => 
            [limit] => 
            [full] => LIMIT 0, 12
        )

)

The way it is structured right now is: where is an array that holds the options in it, so status and category_id are the items i want to appear as WHERE status.. etc. In each there is an op, this is the operation to perform, and after that is the value, so it would end up as Where status != 2.

orderBy just outputs the key and the value, e.g. views ASC.

offsetLimit allows you to either send the offset and limit separate and the function will put it together in the output, or you can send the string of it already in the full setting.

I'm sure its possible to create a function that will handle this and be dynamic to support more options, i just cant get my head around it for some reason at the moment.

Thanks!

EDIT: To be clear, i do not want to get any data from the database with the function, the functions only purpose is to format the data given to it. The array sent to the function will have the table names and everything the function needs in order to FORMAT the string.

dousu8456
dousu8456 我可能还不清楚,但我不会从数据库中获取数据,我将发送给函数的数组将包含所有数据,funxction将只返回一个包含所有数据格式的字符串在里面。我将发送给它的数组已经包含了表名,函数的目的只是将数据结构化为一个在SQL查询中使用的字符串
一年多之前 回复
douzhang6646
douzhang6646 只是说,这是为单个函数编写代码的方法。要做到这一点,它可能超过500行代码。我的意思是通过使用SHOWCOLUMNSFROM表从数据库中动态获取字段名称....一旦你为它添加安全性,这个主题就太宽泛了。我过去写过类似的东西。
一年多之前 回复
dscdttg4389
dscdttg4389 我最终想要的是对函数的调用,例如$string=setQueryString($array)。该函数将根据我发送的数据返回一个字符串。
一年多之前 回复
duanfu6160
duanfu6160 它实际上并不那么难,但要使其安全起来要困难得多。密钥可以由客户端编辑,因此您必须知道表中的所有字段并对其进行清理。SELECT$valueFROM$tableWHERE$something=$value如果没有正确清理,那么这些变量中的任何一个都可用于执行SQLInjection。事实上,在新闻中只有一个带有密钥注入漏洞的wordpress插件。
一年多之前 回复
dongmei2351
dongmei2351 因此,创建一个查询模板,然后用实际值替换一些占位符。
一年多之前 回复

1个回答

I have a working "prototype" of the function, im sure this can be improved and cleaned up, but at the moment its working as it should and formatting the data given to it in the correct way.

Here is the function:

function formatQueryClauseString($querySettings){

    //This is the string placeholder
    $stringBuild = "";

    //Array containing the clause option separators
    $separators = array(
        "where" =>      " AND",
        "order by" =>   ",",
        "limit" =>      ","
    );

    //Loop through the data
    foreach($querySettings AS $clause => $data){

        //Add the clause to the string
        $stringBuild .= " ".$clause;

        //Start an itiration counter
        $i = 0;

        //Loop through the clause settings
        foreach($data AS $key => $value){

            //Add the separators if not first iteration
            if($i > 0){
                $stringBuild .= $separators[strtolower($clause)];
            }

            //Start another iteration counter (used for the limit clause)
            $x = 0;

            //Loop through the clause setting values
            foreach($value AS $val){

                //If its a limit clause, add the separator here
                if(strtolower($clause) == "limit" && $x == 1){
                    $stringBuild .= $separators[strtolower($clause)];
                }

                //Add the value to the string
                $stringBuild .= " ".$val;
                $x = 1;
            }
            $i = 1;
        }
    }

    //Return the formatted string
    return $stringBuild;
}

when given the following array:

Array
(
    [WHERE] => Array
        (
            [1] => Array
                (
                    [column] => status
                    [op] => !=
                    [value] => 2
                )

            [2] => Array
                (
                    [column] => category_id
                    [op] => =
                    [value] => 3
                )

        )

    [ORDER BY] => Array
        (
            [1] => Array
                (
                    [by] => views
                    [order] => ASC
                )

            [2] => Array
                (
                    [by] => date
                    [order] => DESC
                )

        )

    [LIMIT] => Array
        (
            [1] => Array
                (
                    [offset] => 0
                    [limit] => 12
                )
        )
)

It returns this:

WHERE status != 2 AND category_id = 3 ORDER BY views ASC, date DESC LIMIT 0, 12

So overall its working as it should. Ill try and improve it, but maybe it will be useful for others, and maybe others can help improve it too.

Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!
立即提问