Submitting form using dynamic variable with query I have a form that I submit with these variables: category_time, date_month, date_year, and status_time.

Here is an example of different form possibilities:




CATEGORY_TIME     1
DATE_MONTH    Month:
DATE_YEAR     Year:
FIELDNAMES    DATE_MONTH,DATE_YEAR,SUBMIT
STATUS_TIME   Select:
SUBMIT    submit 
----------------
CATEGORY_TIME     Choose:
DATE_MONTH    Month:
DATE_YEAR     Year:
FIELDNAMES    DATE_MONTH,DATE_YEAR,SUBMIT
STATUS_TIME   Pending
SUBMIT    submit 
----------------
CATEGORY_TIME     Choose:
DATE_MONTH    Month:
DATE_YEAR     2016
FIELDNAMES    DATE_MONTH,DATE_YEAR,SUBMIT
STATUS_TIME   Select:
SUBMIT    submit



After I submit the form depending on the variables it outputs a query. For example, with the first possibility only category_time chose, it will output the second if statement.

From what you can see, there's a lot of ifs statements that will be needed. Is there an easier way to make this work without so many ifs statement and just depending on the variables that are chosen, even if I have to use JavaScript to make it work better?



&lt;cfif  structKeyExists(form, "category_time") ||  structKeyExists(form, "date_month")  || structKeyExists(form, "date_year") || structKeyExists(form, "status_time") &gt;
&lt;cfquery ....&gt;
SELECT  *
    FROM work_timeline
    &lt;cfif  (category_time eq 'ALL' || category_time eq 'Choose:') &amp;&amp;  date_month eq 'Month:' &amp;&amp; date_year eq 'Year:' &amp;&amp; status_time eq 'Select:' &gt;
        order by year(date_time) desc, month(date_time) desc
    &lt;/cfif&gt;

    &lt;cfif  category_time neq 'ALL' &amp;&amp; category_time neq 'Choose:' &amp;&amp;  date_month eq 'Month:' &amp;&amp; date_year eq 'Year:' &amp;&amp; status_time eq 'Select:' &gt;
    where category_time = #category_time#
    &lt;/cfif&gt;
    &lt;cfif structKeyExists(form, "category_time") &amp;&amp; date_month neq 'Month:' &amp;&amp; date_year eq 'Year:' &amp;&amp; category_time neq 'Choose:'
        and category_time neq 'ALL' &amp;&amp; status eq 'Select:'&gt;
    where category_time = #category_time# and month(date_time)=#date_month# 
    &lt;/cfif&gt;
    more if's statments .....
&lt;/cfquery&gt;
&lt;/cfif&gt;
A few things you could do to simplify the code:


Rather than examining every form field to determine if the form was submitted, a single check of say - the submit button - should suffice for most purposes. 
Avoid using strings like "ALL" or "Choose:" for the form field values. Those are fine for display purposes, but the field values should be something more generic, like an empty string. That makes for simpler, more uniform, validation code. 

For example, if the default is an empty string, a simple LEN() check is all you need to determine if a particular field was populated. (You can also use other boolean functions like IsNumeric or IsDate, when more appropriate).
Consider using cfparam to assign a default for all fields (except the submit button). That ensures the fields always exist, eliminating the need to use structKeyExists every time you access a field.
With the above changes, you could also simplify the WHERE clause logic. Start with an expression that is always true. Then add additional filters based on which form fields were populated. 

NB: Using raw client supplied values in a queries puts your database at risk for sql injection. Always use cfqueryparam on all parameters. (I do not know the data types of your database columns, so modify the cfsqltype's as needed.)

&lt;cfparam name="FORM.category_time" default=""&gt;
&lt;cfparam name="FORM.date_month" default=""&gt;
...

&lt;cfif structKeyExists(FORM, "submit")&gt;

    &lt;cfquery ....&gt;
        SELECT Column1, Column2, ....
        FROM   work_timeline
        WHERE  1 = 1 

        &lt;!--- Filter on FORM.category_time when populated  ---&gt;
        &lt;cfif LEN(FORM.category_time)&gt;
            AND category_time = &lt;cfqueryparam value="#FORM.category_time#" cfsqltype="cf_sql_integer"&gt;
        &lt;/cfif&gt;

        &lt;!--- Filter on FORM.date_month when populated ---&gt;
        &lt;cfif LEN(FORM.date_month)&gt;
            AND month(date_time) = &lt;cfqueryparam value="#FORM.date_month#" cfsqltype="cf_sql_integer"&gt;
        &lt;/cfif&gt;

        ... more cfif conditions
    &lt;/cfquery&gt;
&lt;/cfif&gt;





As an aside, regarding SQL


There is nothing technically wrong with using SELECT *, but in most cases it is preferable to supply a list of the columns you need. It makes the code a more readable, and also helps avoid pulling back extra data columns that are never used.
Using functions on an indexed database column usually prevents the database from utilizing the index. While it may not be feasible for your current query, when possible it is best to revise expressions like this:

WHERE month(column) = 12 AND year(column) = 2015


.. to use a more index friendly expression instead. For example:

   WHERE column &gt;= '2015-12-01' -- Begin date at midnight       
   AND   column &lt;  '2016-01-01' -- Day AFTER end date at midnight