How to convert array values to MySQL query using PHP SQL Parser?

Question

my Code

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

I'm getting output like below array

Now I want to generate the query using this array. Why am I doing this, later I will add additional parameters to this array. like I pass additional condition in WHERE clause or Table

FOR EXAMPLE: Previous query

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

Now I want to pass two more conditions in where clause like after WHERE condition Customers.CustomerID = "123" and status = "Active" and created_by = 1;

so here my final query is like

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

So how can I achieve it, or Is there any function in PHPSQLPARSER using this array to have any function to generate the query? Thank you for Advance and Sorry for any grammar mistakes

Based on this array, what kind of query do you want to generate, Place a sample Query — ManiMuthuPandi, Mar 21 '18 at 06:33
Same query. but I add additional conditions on where clauses. suppose, In my query, I wrote ' where Customers.CustomerName = "Siddhu"' here I can add an extra filter like 'where Customers.CustomerName = "Siddhu" and id =1 and created_on = '2018-03-21'' like this. maybe add extra joins. so I create an array of a mentioned array. finally, I want to convert entire into query @ManiMuthuPandi — Siddhu, Mar 21 '18 at 06:37
@Siddhu What is the problem you are facing right now? Since you have the array what is the problem in building the SQL statement? Where are you struggle with your code? Please edit your question to include the code which tries to convert the array to an SQL query and explain what exactly isn't working. — Progman, Mar 21 '18 at 18:52
Pass to the additional array to where condition. Then concatenate the string to query — Siddhartha esunuri, Mar 26 '18 at 07:10
The library contains something called PHPSQLCreator which convert this array back to an SQL statement — ahmad, Mar 26 '18 at 16:59

score 2 · Accepted Answer · edited Jun 20 '20 at 09:12

To build a query from the Array, PHPSQLParser has a creator 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 an array, you can passe your own Array

echo $creator->create($myArray);

To add a condition to the array, you can add it to the WHERE array of conditions

each condition has 3 arrays defining colref ( the column name ), operator ( well .. operator ) and const ( the value )

the tricky part is the position in the sub array of the WHERE as you need to specify where exactly you want to insert each one of those three, so based on the WHERE in the example you provided , you can see that the position of the operator = is 169 ( starting from 0 )

check this tool to see character position in a string ( this starts from 1 ).

And based on this Complexe example

Your final WHERE Array should look like this (i'm not sure if you need the [no_quotes] key though) :

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.

Nik · Answer 2 · 2018-03-27T21:44:43.713

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.

How to convert array values to MySQL query using PHP SQL Parser?

2 Answers2