Return each value only once using cfloop query

[Emily_LaMunyon]

Hi,

I have some code that dynamically populates a cfselect list and then populates a second list based on those values. Everything seems to be working fine, except that all values are being pulled from the database, and there are duplicates in most cases. For example, value 1S1W diplays in the drop-down menu 10 times, where I would only like it there once.

Is there a way to have one of each value in my list?

<cfif isDefined('form.select_Main_Group')>

    <cfset page.select_Main_Group = form.select_Main_Group>

</cfif>

<cfoutput>

  <form name="DropDown" method="post">

  <!--- query DB for the first drop down list --->

<CFQUERY name="get_Main_Group" datasource="ds" dbtype="odbc">

SELECT *

          FROM  slco_sire.dbo.area_reference_plats_doc INNER JOIN slco_sire.dbo.area_reference_plats_page

          ON    slco_sire.dbo.area_reference_plats_doc.doc_id = slco_sire.dbo.area_reference_plats_page.doc_id

          ORDER BY page_description

</CFQUERY>

  <!--- first drop down list --->

  <!--- NOTICE the onChange javascript event in the select tag, this is what submits the form after the first selection --->

  <select name="select_Main_Group" required="yes" onchange="this.form.submit()">

     <option>Select Township/Range</option>

     <!--- dynamically populate the first drop down list based on the get_Main_Group query --->

     <!--- NOTICE the CFIF within the option tag, this says, if the first selection has been made, display the chosen option when the page reloads --->

     <cfloop query="get_Main_Group">

         <option value="#SECTION#" <cfif isDefined('form.select_Main_Group')><cfif form.select_Main_Group eq "#SECTION#">selected</cfif></cfif>>#TOWNSHIP_RANGE#</option>

           </cfloop>

</select>

<p>

<!--- if the first selection has been made, display the second drop down list with the appropriate results --->

<cfif isDefined('page.select_Main_Group')>

   <!--- query DB for second drop down list, based on the selected item from the first list --->

   <cfquery name="get_Sub_Group" datasource="ds" dbtype="odbc">

        SELECT TOWNSHIP_RANGE, SECTION

        FROM  slco_sire.dbo.area_reference_plats_doc INNER JOIN slco_sire.dbo.area_reference_plats_page

                    ON    slco_sire.dbo.area_reference_plats_doc.doc_id = slco_sire.dbo.area_reference_plats_page.doc_id

        WHERE SECTION = '#page.select_Main_Group#'

   </cfquery>

   <!--- second drop down list --->

   <select name="select_Sub_Group" required="yes">

      <option>Select Section</option>

      <!--- dynamically populate the second drop down list based on the get_Sub_Group query --->

      <cfloop query="get_Sub_Group">

         <option value="#SECTION#">#SECTION#</option>

      </cfloop>

   </select>

</cfif>

</form>

</cfoutput>

[Miguel-F]

Are your duplicate values coming from the database query?  If so, just add a DISTINCT to your select statement.

http://www.w3schools.com/sql/sql_distinct.asp

[Emily_LaMunyon]

Thanks for the response Miguel!

I tried your suggestion and had a little luck, the second list seems to be working, but I am still getting duplicate TOWNSHIP_RANGE values in the first list.

<cfif isDefined('form.select_Main_Group')>

    <cfset page.select_Main_Group = form.select_Main_Group>

</cfif>

<cfoutput>

  <form name="DropDown" method="post">

  <!--- query DB for the first drop down list --->

<CFQUERY name="get_Main_Group" datasource="ds" dbtype="odbc">

SELECT  DISTINCT TOWNSHIP_RANGE, SECTION

          FROM  slco_sire.dbo.area_reference_plats_doc INNER JOIN slco_sire.dbo.area_reference_plats_page

          ON    slco_sire.dbo.area_reference_plats_doc.doc_id = slco_sire.dbo.area_reference_plats_page.doc_id

          GROUP BY TOWNSHIP_RANGE, SECTION

</CFQUERY>

  <!--- first drop down list --->

  <!--- NOTICE the onChange javascript event in the select tag, this is what submits the form after the first selection --->

  <select name="select_Main_Group" required="yes" onchange="this.form.submit()">

     <option>Select Township/Range</option>

     <!--- dynamically populate the first drop down list based on the get_Main_Group query --->

     <!--- NOTICE the CFIF within the option tag, this says, if the first selection has been made, display the chosen option when the page reloads --->

     <cfloop query="get_Main_Group">

         <option value="#SECTION#" <cfif isDefined('form.select_Main_Group')><cfif form.select_Main_Group eq "#SECTION#">selected</cfif></cfif>>#TOWNSHIP_RANGE#</option>

           </cfloop>

</select>

<p>

<!--- if the first selection has been made, display the second drop down list with the appropriate results --->

<cfif isDefined('page.select_Main_Group')>

   <!--- query DB for second drop down list, based on the selected item from the first list --->

   <cfquery name="get_Sub_Group" datasource="ds" dbtype="odbc">

        SELECT DISTINCT SECTION

        FROM  slco_sire.dbo.area_reference_plats_doc INNER JOIN slco_sire.dbo.area_reference_plats_page

                    ON    slco_sire.dbo.area_reference_plats_doc.doc_id = slco_sire.dbo.area_reference_plats_page.doc_id

        WHERE SECTION = '#page.select_Main_Group#'

                    GROUP BY SECTION

   </cfquery>

   <!--- second drop down list --->

   <select name="select_Sub_Group" required="yes">

      <option>Select Section</option>

      <!--- dynamically populate the second drop down list based on the get_Sub_Group query --->

      <cfloop query="get_Sub_Group">

         <option value="#SECTION#">#SECTION#</option>

      </cfloop>

   </select>

</cfif>

</form>

</cfoutput>

[Miguel-F]

Yes but I am assuming that each TOWNSHIP_RANGE has a unique SECTION since you are returning both of those from your query.  Right?  Your list is displaying the TOWNSHIP_RANGE but the value is the SECTION.  Would it make sense to display both values in the list (TOWNSHIP_RANGE - SECTION)?

[Emily_LaMunyon]

Thanks again for your help.

I am sorry if I was not clear, probably because I am not clear ☺, but each TOWNSHIP_RANGE could have more than one section, but the way the list is now, each value in the TOWNSHIP_RANGE is corresponding to one SECTION even though the TOWNSHIP_RANGE are repeating.

It might make sense to have both values in the list…any ideas how to get started?

Thanks!

[Miguel-F]

I didn't mean having both values really, just show both values in the list but you can still send only the SECTION if that is all you need.  Something like this:

    <cfloop query="get_Main_Group">

         <option value="#SECTION#" <cfif isDefined('form.select_Main_Group')><cfif form.select_Main_Group eq "#SECTION#">selected</cfif></cfif>>#TOWNSHIP_RANGE# - #SECTION#</option>

    </cfloop>

So that visually it makes sense to the user but you still only pass the single value of SECTION in the form.

[Emily_LaMunyon]

Ooh, great idea!! I think this will help a lot.

I will give it a shot.

[Carl Von Stetten]

Emily,

I've been trying to wrap my head around this, and I'm struggling a bit.  Should the first pull-down provide a unique list of Township/Ranges, or Sections?  Is the second pull-down just Sections that match the selection from the first list?

[Emily_LaMunyon]

Hi Carl,

Thanks for your response. What I am trying to achieve is a first list of TOWNSHIP_RANGE that is unique, even though there are duplicates in the database. Then in the second list, populate only those SECTIONS that are available in the selected TOWNSHIP_RANGE. I realize my code may not reflect this, I am a bit stuck and not even sure this is possible….

[Carl Von Stetten]

Emily,

OK.  Yes it is possible.  Change your first query to:

SELECT  DISTINCT TOWNSHIP_RANGE

          FROM  slco_sire.dbo.area_reference_plats_doc INNER JOIN slco_sire.dbo.area_reference_plats_page

          ON    slco_sire.dbo.area_reference_plats_doc.doc_id = slco_sire.dbo.area_reference_plats_page.doc_id

          ORDER BY TOWNSHIP_RANGE

</CFQUERY>

Notice I removed any mention of SECTION, because it's irrelevant to generating a unique list of TOWNSHIP_RANGE.  Also, GROUP BY is not needed, but an ORDER BY will sort the records in alphanumeric order.

Then you need to modify your code where you generate the option list for the first pulldown:

<option value="#get_Main_Group.TOWNSHIP_RANGE#" <cfif StrucKeyExists(form,'select_Main_Group') AND form.select_Main_Group eq "#get_Main_Group.TOWNSHIP_RANGE#">selected</cfif>>#get_Main_Group.TOWNSHIP_RANGE#</option>

Here I did a couple of things beside changing SECTION references to TOWNSHIP_RANGE .  It's a good idea to add the query name prefix to column names when you output them so that you avoid collisions with variables that have the same name.  I also reduced your <CFIF> statements to one compound statement for simplicity, and replaced the IsDefined with a StructKeyExists function.  This is considered to be better practice and won't throw an error when used in a compound statement (if you kept the IsDefined statement and tried that in a compound selection, it can sometimes throw errors if the variable in the IsDefined doesn't exist).

Now, your second query should be something like:

<cfquery name="get_Sub_Group" datasource="ds" dbtype="odbc">

        SELECT DISTINCT SECTION

        FROM  slco_sire.dbo.area_reference_plats_doc INNER JOIN slco_sire.dbo.area_reference_plats_page

                    ON    slco_sire.dbo.area_reference_plats_doc.doc_id = slco_sire.dbo.area_reference_plats_page.doc_id

        WHERE TOWNSHIP_RANGE = '#page.select_Main_Group#'

        ORDER BY SECTION

   </cfquery>

Here I changed the WHERE clause to filter based on the TOWNSHIP_RANGE you selected in the first pull-down.  This should now return a list of SECTIONs that match the TOWNSHIP_RANGE selected in the first pull-down.

Lastly, some minor changes to the option list for the second pulldown:

  <option value="#get_Sub_Group.SECTION#">#get_Sub_Group.SECTION#</option>

One other thing I noticed.  If you are on ColdFusion 8 or 9, the dbytpe="odbc" is no longer needed.  I'm assuming you are using an ODBC database connection to something like MS Access.  If so, you can omit the dbytype entirely.  It's only needed now if you do query-of-query queries, where it would be dbtype="query".

HTH,

-Carl V.

[Emily_LaMunyon]

Wow, Carl, thanks a trillion!!

I have implemented the changes you suggested and am getting one error that reads STRUCTKEYEXISTS is undefined.

I hope I am not messing up what you provided.

<cfif isDefined('form.select_Main_Group')>

    <cfset page.select_Main_Group = form.select_Main_Group>

</cfif>

<cfoutput>

  <form name="DropDown" method="post">

  <!--- query DB for the first drop down list --->

<CFQUERY name="get_Main_Group" datasource="sire_prod" >

          SELECT  DISTINCT TOWNSHIP_RANGE

          FROM  slco_sire.dbo.area_reference_plats_doc INNER JOIN slco_sire.dbo.area_reference_plats_page

          ON    slco_sire.dbo.area_reference_plats_doc.doc_id = slco_sire.dbo.area_reference_plats_page.doc_id

          ORDER BY TOWNSHIP_RANGE

</CFQUERY>

  <!--- first drop down list --->

  <!--- NOTICE the onChange javascript event in the select tag, this is what submits the form after the first selection --->

  <select name="select_Main_Group" required="yes" onchange="this.form.submit()">

     <option>Select Township/Range</option>

     <!--- dynamically populate the first drop down list based on the get_Main_Group query --->

     <!--- NOTICE the CFIF within the option tag, this says, if the first selection has been made, display the chosen option when the page reloads --->

     <cfloop query="get_Main_Group">

        <option value="#get_Main_Group.TOWNSHIP_RANGE#" <cfif StrucKeyExists(form,'select_Main_Group') AND form.select_Main_Group eq "#get_Main_Group.TOWNSHIP_RANGE#">selected</cfif>>#get_Main_Group.TOWNSHIP_RANGE#</option>

           </cfloop>

</select>

<p>

<!--- if the first selection has been made, display the second drop down list with the appropriate results --->

<cfif isDefined('page.select_Main_Group')>

   <!--- query DB for second drop down list, based on the selected item from the first list --->

  <cfquery name="get_Sub_Group" datasource="sire_prod">

        SELECT DISTINCT SECTION

        FROM  slco_sire.dbo.area_reference_plats_doc INNER JOIN slco_sire.dbo.area_reference_plats_page

        ON    slco_sire.dbo.area_reference_plats_doc.doc_id = slco_sire.dbo.area_reference_plats_page.doc_id

        WHERE TOWNSHIP_RANGE = '#page.select_Main_Group#'

        ORDER BY SECTION

   </cfquery>

   <!--- second drop down list --->

   <select name="select_Sub_Group" required="yes">

      <option>Select Section</option>

      <!--- dynamically populate the second drop down list based on the get_Sub_Group query --->

      <cfloop query="get_Sub_Group">

          <option value="#get_Sub_Group.SECTION#">#get_Sub_Group.SECTION#</option>

      </cfloop>

   </select>

</cfif>

</form>

</cfoutput>

[Emily_LaMunyon]

Carl, I am sorry, I figured out the problem, there was a “t” missing in StructKeyExists, thanks so much, it is working now. ☺

[Carl Von Stetten]

Glad it's working for you now!

[Emily_LaMunyon]

Sorry, one more thing and I totally understand if no one replies. I am having my form action submit to another page and it is submitting before the next drop-down list appears, which is throwing an error. Is there a way around this?

<cfif isDefined('form.select_Main_Group')>

    <cfset page.select_Main_Group = form.select_Main_Group>

</cfif>

<cfoutput>

  <cfform name="test" method="post" action="by_TRS_results_arps_2.cfm">

  <!--- query DB for the first drop down list --->

<CFQUERY name="get_Main_Group" datasource="ds" >

          SELECT  DISTINCT TOWNSHIP_RANGE

          FROM  slco_sire.dbo.area_reference_plats_doc INNER JOIN slco_sire.dbo.area_reference_plats_page

          ON    slco_sire.dbo.area_reference_plats_doc.doc_id = slco_sire.dbo.area_reference_plats_page.doc_id

          ORDER BY TOWNSHIP_RANGE

</CFQUERY>

  <!--- first drop down list --->

  <!--- NOTICE the onChange javascript event in the select tag, this is what submits the form after the first selection --->

  <cfselect name="select_Main_Group" required="yes" onchange="this.form.submit()">

     <option>Select Township/Range</option>

     <!--- dynamically populate the first drop down list based on the get_Main_Group query --->

     <!--- NOTICE the CFIF within the option tag, this says, if the first selection has been made, display the chosen option when the page reloads --->

     <cfloop query="get_Main_Group">

        <option value="#get_Main_Group.TOWNSHIP_RANGE#" <cfif StructKeyExists(form,'select_Main_Group') AND form.select_Main_Group eq "#get_Main_Group.TOWNSHIP_RANGE#">selected</cfif>>#get_Main_Group.TOWNSHIP_RANGE#</option>

           </cfloop>

</cfselect>

<p>

<!--- if the first selection has been made, display the second drop down list with the appropriate results --->

<cfif isDefined('page.select_Main_Group')>

   <!--- query DB for second drop down list, based on the selected item from the first list --->

  <cfquery name="get_Sub_Group" datasource="ds">

        SELECT DISTINCT SECTION

        FROM  slco_sire.dbo.area_reference_plats_doc INNER JOIN slco_sire.dbo.area_reference_plats_page

        ON    slco_sire.dbo.area_reference_plats_doc.doc_id = slco_sire.dbo.area_reference_plats_page.doc_id

        WHERE TOWNSHIP_RANGE = '#page.select_Main_Group#'

        ORDER BY SECTION

   </cfquery>

   <!--- second drop down list --->

   <select name="select_Sub_Group" required="yes">

      <option>Select Section</option>

      <!--- dynamically populate the second drop down list based on the get_Sub_Group query --->

      <cfloop query="get_Sub_Group">

          <option value="#get_Sub_Group.SECTION#">#get_Sub_Group.SECTION#</option>

      </cfloop>

   </select>

</cfif>

<br><INPUT TYPE="submit" VALUE="GO">

</cfform>

</cfoutput>

[Carl Von Stetten · Accepted solution]

Emily,

Easy fix.  Use the same approach as you did to determine when to show the second drop-down.  Change this line:

<cfform name="test" method="post" action="by_TRS_results_arps_2.cfm">

to these:

<cfif isDefined('page.select_Main_Group')>

     <cfform name="test" method="post" action="by_TRS_results_arps_2.cfm">

<cfelse>

     <cfform name="test" method="post">

</cfif>

[Emily_LaMunyon]

You are soooo awesome!!! Thanks it working ☺

[Emily_LaMunyon]

This is working well, as I mentioned. However, I am having one more issue with my related select lists.

The first time a selection is made in the first list (TOWNSHIP_RANGE), the second related list populates with the correct corresponding values (SECTIONS), however, if another selction is made in the first list, it appears that the second list does not update with the values that match the new selction in the first list. Is there a way to reload or refresh the second list so the values always correspond with the selection in the first list? 

<cfif isDefined('form.select_Main_Group')>

    <cfset page.select_Main_Group = form.select_Main_Group>

</cfif>

<cfoutput>

  <cfif isDefined('page.select_Main_Group')>

     <form name="test" method="post" target="_BLANK" action="by_TRS_results_arps_2.cfm">

<cfelse>

     <form name="test" method="post">

</cfif>

  <!--- query DB for the first drop down list --->

<CFQUERY name="get_Main_Group" datasource="sire_prod" >

          SELECT  DISTINCT TOWNSHIP_RANGE

          FROM  slco_sire.dbo.area_reference_plats_doc INNER JOIN slco_sire.dbo.area_reference_plats_page

          ON    slco_sire.dbo.area_reference_plats_doc.doc_id = slco_sire.dbo.area_reference_plats_page.doc_id

          ORDER BY TOWNSHIP_RANGE

</CFQUERY>

  <!--- first drop down list --->

  <!--- NOTICE the onChange javascript event in the select tag, this is what submits the form after the first selection --->

  <select name="select_Main_Group" required="yes" onchange="this.form.submit()">

     <option>Select Township/Range</option>

     <!--- dynamically populate the first drop down list based on the get_Main_Group query --->

     <!--- NOTICE the CFIF within the option tag, this says, if the first selection has been made, display the chosen option when the page reloads --->

     <cfloop query="get_Main_Group">

        <option value="#get_Main_Group.TOWNSHIP_RANGE#" <cfif StructKeyExists(form,'select_Main_Group') AND form.select_Main_Group eq "#get_Main_Group.TOWNSHIP_RANGE#">selected</cfif>>#get_Main_Group.TOWNSHIP_RANGE#</option>

           </cfloop>

</select>

<p>

<!--- if the first selection has been made, display the second drop down list with the appropriate results --->

<cfif isDefined('page.select_Main_Group')>

   <!--- query DB for second drop down list, based on the selected item from the first list --->

  <cfquery name="get_Sub_Group" datasource="sire_prod">

        SELECT DISTINCT SECTION

        FROM  slco_sire.dbo.area_reference_plats_doc INNER JOIN slco_sire.dbo.area_reference_plats_page

        ON    slco_sire.dbo.area_reference_plats_doc.doc_id = slco_sire.dbo.area_reference_plats_page.doc_id

        WHERE TOWNSHIP_RANGE = '#page.select_Main_Group#'

        ORDER BY SECTION

   </cfquery>

   <!--- second drop down list --->

   <select name="select_Sub_Group" required="yes">

      <option>Select Section</option>

      <!--- dynamically populate the second drop down list based on the get_Sub_Group query --->

      <cfloop query="get_Sub_Group">

          <option value="#get_Sub_Group.SECTION#">#get_Sub_Group.SECTION#</option>

      </cfloop>

   </select>

</cfif>

<br><INPUT TYPE="submit" VALUE="GO">

</form>

</cfoutput>

[Carl Von Stetten]

Emily,

You'll have to tap into JavaScript for this.  Take a look at the onChange attribute for <select> elements.  You can write a JavaScript function to clear the 2nd select element and resubmit the form, then call this function from the onChange attribute of the first select element.

HTH,

-Carl V.

[Dan_Bracuk]

The group attribute of cfoutput might be useful.