Highlighted

Form not filtering results

Explorer ,
Apr 21, 2015

Copy link to clipboard

Copied

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>

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()

Views

368

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more

Form not filtering results

Explorer ,
Apr 21, 2015

Copy link to clipboard

Copied

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>

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()

Views

369

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Apr 21, 2015 0
Advocate ,
Apr 22, 2015

Copy link to clipboard

Copied

Your options in the dropdown are blank????

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Apr 22, 2015 0
Explorer ,
Apr 22, 2015

Copy link to clipboard

Copied

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>

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Apr 22, 2015 0
Adobe Employee ,
Apr 22, 2015

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Apr 22, 2015 0
Explorer ,
Apr 22, 2015

Copy link to clipboard

Copied

That didn't work.  I tried it and when I make a selection and then click the submit button nothing happens.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Apr 22, 2015 0
Advocate ,
Apr 22, 2015

Copy link to clipboard

Copied

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()

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Apr 22, 2015 0
Explorer ,
Apr 22, 2015

Copy link to clipboard

Copied

Thank you that worked.  Not sure why I used val(). It was an example I found searching online.  Thank you so much for your help.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Apr 22, 2015 1