how to create dynamic SQL using PHP
Posted: Wed Jul 14, 2010 3:44 pm
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
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