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

Question

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.

Answer 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.