Assuming that you're doing some sort of query-by-example (QBE) form, where the WHERE clause you're generating depends on which form fields have non-empty cells, you can do build a query on-the-fly and use bind parameters by doing something like the following:
As you're scanning your form fields, build an array that holds records (anonymous arrays) of the form (field, relop, value) where relop defaults to '='. (This scheme allows you to specify things like "> 47".)
You can then scan this array to build the WHERE clause. You have two options here. You could translate directly to field relop value and then join() these fragments with an AND, or you could translate to field relop ? (being careful to correctly quote the values) before joining with an AND. Then, prepare() the query. In the later case, you need to scan your array a second time to extract an array of values to pass to execute().
If your application can benefit from caching queries, you can use a concatenation of field names as a key to retrieve a previously-prepared query. E.g., after you construct and prepare a query, save the statement handle using the fields in the WHERE clause as a key.
This scheme can be adjusted to accomodate both IS [NOT] NULL (by decorating the field name as used in the key) and BETWEEN clauses (by repeating the field name).
In reply to Re: Database queries w/lots of inputs.
by dws
in thread Database queries w/lots of inputs.
by elam
| For: | Use: | ||
| & | & | ||
| < | < | ||
| > | > | ||
| [ | [ | ||
| ] | ] |