How to convert array values to MySQL query using PHP SQL Parser?
Get the solution ↓↓↓Solution:
To build a query from the Array,PHPSQLParser
has acreator
method,
From the documentation here : 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;
of course since$parser->parsed
is anarray
, you can passe your own Array
echo $creator->create($myArray);
To add a condition to the array, you can add it to theWHERE
array of conditions
each condition has 3 arrays definingcolref
( the column name ),{-code-10}
( well .. {-code-10} ) andconst
( the value )
the tricky part is theposition
in the sub array of theWHERE
as you need to specify where exactly you want to insert each one of those three, so based on theWHERE
in the example you provided , you can see that the position of the {-code-10}=
is169
( starting from0
)
check this tool to see character position in a string ( this starts from 1 ).
And based on this Complexe example
Your finalWHERE
Array should look like this (i'm not sure if you need the[no_quotes]
key though) :
[WHERE] => Array
(
[0] => Array
(
[expr_type] => colref
[base_expr] => customers.customername
[no_quotes] => customers.customername
[sub_tree] =>
[position] => 146
)
[1] => Array
(
[expr_type] => {-code-10}
[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] => {-code-10}
[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] => {-code-10}
[base_expr] => =
[sub_tree] =>
[position] => 205
)
[6] => Array
(
[expr_type] => const
[base_expr] => "123"
[sub_tree] =>
[position] => 207
)
[7] => Array
(
[expr_type] => {-code-10}
[base_expr] => and
[sub_tree] =>
[position] => 213
)
[8] => Array
(
[expr_type] => colref
[base_expr] => status
[no_quotes] => status
[position] => 217
)
[9] => Array
(
[expr_type] => {-code-10}
[base_expr] => =
[sub_tree] =>
[position] => 224
)
[10] => Array
(
[expr_type] => const
[base_expr] => "Active"
[sub_tree] =>
[position] => 226
)
[11] => Array
(
[expr_type] => {-code-10}
[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] => {-code-10}
[base_expr] => =
[sub_tree] =>
[position] => 250
)
[14] => Array
(
[expr_type] => const
[base_expr] => 1
[sub_tree] =>
[position] => 252
)
)
PS : i used the query with multiple conditions you provided and took off indentation and line breaks to figure out the positions, play around with those values if you don't have the desired string output as this is supposed to be just an example.
i hope this helps or at least gives you an idea and Good Luck.
Answer
Solution:
Honestly, I admit I was not able to fully understand the question. But trying to answer it from what I was able to comprehend.
I believe you want to use the output from first query and generate another query with additional where clause. You might just be able to do that by a simple additional select clause with CONCAT in original query itself. Concat your hardcoded original query with desired columns and generate your dynamic SQL as an additional output column.
SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate,
CONCAT("SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate FROM Orders LEFT JOIN Customers ON Orders.CustomerID=Customers.CustomerID WHERE Customers.CustomerName = \"Siddhu\"", " AND Customers.CustomerID = \"", Customers.CustomerID, " and status = \"Active\" and created_by = 1;")
FROM Orders
LEFT JOIN Customers ON Orders.CustomerID=Customers.CustomerID
WHERE Customers.CustomerName = "Siddhu"
If the status field is also coming from one of the tables then you can break the CONCAT function and use that column name instead. Hope it helps.
Share solution ↓
Additional Information:
Link To Answer People are also looking for solutions of the problem: uncaught error: call to undefined function mysqli_connect()
Didn't find the answer?
Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.
Similar questions
Find the answer in similar questions on our website.
Write quick answer
Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.