Skip to main content
catherine26
Inspiring
Apr 21, 2015 4:46 PM UTC
Answered

Form not filtering results

I'm creating 2 filtering drop downs and I can't figure out why my code isn't filtering the results on the page.  Please help...

Here's the code.

<cfparam name="form.personnel" default="">

<cfparam name="form.status" default="">

<cfquery name="q" datasource="#dsn#">

     SELECT *

     FROM table

     WHERE 1=1

          <cfif val(form.personnel) GT 0>

               AND Sector = <cfqueryparam value="#form.personnel#" cfsqltype="cf_sql_varchar">

          </cfif>

          <cfif val(form.status) GT 0>

               AND reqStatus = <cfqueryparam value="#form.status#" cfsqltype="cf_sql_varchar">

          </cfif>

</cfquery>

<form method="post" action="">

     <select name="personnel">

               <option></option>

     </select>

     <select name="status">

               <option></option>

     </select>

     <input type="submit" />

</form>

<cfoutput query="q">

     #sector# || #reqstatus#

</cfoutput>

    This topic is closed to new replies. Start a new post to keep the conversation going.
    haxtbhCorrect answer
    Inspiring
    Apr 22, 2015 12:01 PM UTC

    Why are you checking "<cfif val(form.personnel) GT 0>" in the SQL query? What are your reasons for using val()? Your results are most likely not being filtered as your SQL query is not working as you are checking your variables wrong.

    Are you trying to determine that the form string is not empty? In that case you want to use len()

    View reply

    6 replies

    haxtbh
    Inspiring
    Apr 22, 2015 7:50 AM UTC

    Your options in the dropdown are blank????

    Translate
    catherine26Author
    Inspiring
    Apr 22, 2015 10:23 AM UTC

    Sorry I was trying to short cut the coding.  There are actually options in the drop down.

    Like this...

    <form method="post" action="">
         <select name="sector">

              <option value="">All Personnel</option>

              <option value="Air Force">Air Force</option>

              <option value="Army">Army</option>

              <option value="Navy">Navy</option>

              etc...

         </select>

         <select name="status">

              <option value="">All Statuses</option>

              <option value="Active">Active</option>

              <option value="Completed">Completed</option>

              etc...

         </select>

         <input type="submit" />

    </form>

    Translate
    Anit_Kumar
    Anit_Kumar
    Inspiring
    Apr 22, 2015 10:51 AM UTC

    You can try this:-

    <form method="post" action="">

         <select name="sector">

              <option value="">All Personnel</option>

              <option value="Air Force">Air Force</option>

              <option value="Army">Army</option>

              <option value="Navy">Navy</option>

              etc...

         </select>

         <select name="status">

              <option value="">All Statuses</option>

              <option value="Active">Active</option>

              <option value="Completed">Completed</option>

              etc...

         </select>

         <input type="submit" />

    </form>

    <cfif IsDefined("form.sector")>

      <cfoutput>sector='#sector#' and status='#status#'</cfoutput>

    </cfif>

    Regards,

    Anit Kumar

    Translate
    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices