Page 1 of 1

how to create dynamic SQL using PHP

Posted: Wed Jul 14, 2010 3:44 pm
by rizjav_86
ok so I am currently working on a project "Dynamic Reports", it basically allows user to select any number of fields and the filter for each field and then it generates a reports based on the user selection.

Let Say I have following front-end:

Table1
Field A
Field B

Table2
Field C
Field D

now let say if a user select "Field A" of Table1 and in the criteria he puts X and he selects = sign as an operator, then I will get it as a request variable in this form [Table Name].[Field Name]~[Operator]~[Value] i.e. for the given example I will have a following request variable Table1.Field A~=~'X'

My question is how do I generate SQL query for SQL from these request variables. Here is what I am currently doing and I got some success but it doesn't works for all the cases.
I created 3 arrays: Select, From, Where. I loop through all the request variables and extract the appropriate values and put them in their respective arrays.

so according to the given example, my arrays would look like this:

Select = Array ([0] => Field A)
From = Array ([0] => Table1)
Where = Array ([0] => Field A = 'X')

then I convert the arrays into string and form the query, but it gives me wrong values if I select the same filed more than once...e.g. if I want to see Field A ='X' or Field A = 'Y' then it wont work.

Any ideas, comments and suggestions are much appreciated !

Thanks

Re: how to create dynamic SQL using PHP

Posted: Thu Jul 15, 2010 2:53 pm
by JakeJ
Why don't you post the code and point out specifically what is working and what is not and where you think it's going wrong.

That would be very helpful.