form based query on the fly

amrigo

I would like to create a query on the fly based on 8 checkboxes that the user can choose from, so basically i do concate them like:

/***  select ***/
 $query_str = "SELECT ";
 if ($_POST[field1]) { $query_str .= "FIELD1 "; }
 if ($_POST[field1]) { $query_str .= "FIELD2 "; }

/ From /
$from_str = "FROM ";
if ($POST[field1]) { $from_str .= "TABLE 1 "; }
if ($POST[field1]) { $from_str .= "TABLE 2"; }
....
/ where /
$where_str="WHERE ";
if ($POST[field1]) { $where_str .= "FIELD1 = '$POST[field1]'"; }
if ($POST[field1]) { $where_str .= "FIELD2 = '$POST[field2]'"; }
....
/ and /
$and_str="AND ";
if ($POST[field1]) { $and_str .= "FIELD1 = '$POST[natureza]'"; }
if ($POST[field1]) { $and_str .= "FIELD2 = '$POST[municipio]'"; }
....

$sql_str = $query_str.$from_str.$where_str.$and_srt;

My goal is to handle if more than one option is choosed and the AND is needed on the query , also how can i concatenate the tables if more than one table is choosed

Thanks in advance

vincery

Hi,

Can you show me your FORM's html
code?

amrigo

it´s something like:

<input type="checkbox" name="item1"> Item 1
//next 7 itens

then the next file shows the select boxes or input textfields wich the user has choosen ...(it can be 8 at a time)

My problem is that choosen more than one at a time the query is not being written the right way

vincery

For me your approach is fine assuming
that a user will select a field and a
matching table or vice versa. Have you
taken into consideration where a user
may select a field in a different
table? I think this would cause an
error during the query because it
would be selecting an unknown column
from a specific table.

But I do not know know exactly the
structure of your database and that
somehow, you have some reasons for
doing it that way. Again, your
approach is fine but I can see that you need some refinements to your concatenation lines. E.g. You have to
put a comma between the selected
columns.

amrigo

Yes if the user select more than one option the query will be wrong,

for example: the same table will be displayed two times in the FROM section.

As i have only two diferent tables

One is called for 2 itens and the other is called for 6 itens

The WHERE section must have just one item and the rest of the itens choosed must be in the AND section

But the WHERE clausule is displayed for all the options the user choose

Also the AND section have just one and and for the rest of the options choosed it display only the matching field and not the AND + matching field.