[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Cfouput distinct items for a field

Posted on 2007-10-14
5
Medium Priority
?
264 Views
Last Modified: 2008-05-13
I have a table that I want to output into a series of drop down menus.  Each field has duplicate entries that cannot be removed.  Using a query I want to pull all of the data from the table at once.   I only want to display the distinct entries from each field.  I cannot use SELECT DISTINCT in the query though because all entries are distinct.  I need to be able to pull distinct fields and all data at the same time.    I put an example below that I hope explains what my table looks like a little better.

hierarchyID   domain                          directory     subdirectory      subdirectory2
1                   www.mysite.com          about            docs                 images      
2                   www.mysite.com          about            docs                 forms    
3                   www.mysite.com          about            contact
4                   www.mysite.com          forms          
5                   www.mysite.com          forms            images

My query is

<cfquery name="gethierarchy" datasource="Review">
SELECT  *
FROM hierarchy
ORDER BY hierarchyID ASC
</cfquery>

I would like to display the distinct directory, subdirectory,...   For example I'll want to pull only subdirectories in the "about" directory.  As you can see in my example table above that there are duplicate entries under each field but the complete database entry is not a duplicate.   Is there a way to do something like <cfouput query="gethierarchy">DISTINCT(#directory#) so that I can just run the query once but output the distinct items?

0
Comment
Question by:ccnorris
  • 2
  • 2
5 Comments
 
LVL 10

Accepted Solution

by:
js_vaughan earned 1800 total points
ID: 20074858
If you need to have the dropdowns update based on prior selections you will either need to use Javascript or AJAX.  The javascript route would preload all of the possible values to the browser, where AJAX would update each one real time.  You can find out more by researching "dynamic dropdown" with javascript or Coldfusion.

Now, if you don't need the dropdowns to update without a reload, then you can simply solve the problem with a query-of-query.  Query-of-queries read from the original query already loaded in memory so you don't have to run back to the database a second time.  For example, to get only the subdirectories for the "about" directory, here is the code:

<!--- Get all data first --->
<cfquery name="gethierarchy" datasource="Review">
SELECT  *
FROM hierarchy
ORDER BY hierarchyID ASC
</cfquery>

<!--- Get directory-specific data --->
<cfquery dbtype="query">
SELECT subdirectory
FROM gethierarchy
WHERE directory = "about"
</cfquery>
0
 
LVL 10

Assisted Solution

by:js_vaughan
js_vaughan earned 1800 total points
ID: 20074870
Also, if you just need to output all the dropdowns (without javascript or AJAX) then you can also use CFOUTPUT's GROUP attribute.  More info: http://livedocs.adobe.com/coldfusion/6.1/htmldocs/tags-b12.htm
0
 

Author Comment

by:ccnorris
ID: 20075727
OK. Here is a little more information. I want to do something similiar to below.  I had to add a line to the query.  Can I use either of your suggestions for this?

<cfquery name="gethierarchy" datasource="Review">
SELECT *
FROM hierarchy
WHERE directory = '#url.formdir#'
ORDER BY hierarchyID ASC
</cfquery>

<cfif subdirectory neq "">
    <cfif subdirectory neq "">
       <select name="subdirectory" size="1">
       <option value="">Select subdirectory</option>
       <option value="#subdirectory#">#subdirectory#</option>
       <option value="#subdirectory2#">&nbsp;&nbsp;&nbsp;#subdirectory2#</option>
       </select>
      </cfif>
  <cfelse>
      <select name="subdirectory" size="1">
      <option value="">Select subdirectory</option>
      <option value="#subdirectory#">#subdirectory#</option>
      </select>
  </cfif>
0
 
LVL 58

Assisted Solution

by:harfang
harfang earned 200 total points
ID: 20078901
Barring any special cold fusion command to compact a list of options in an options list, you will need to requery your table at each change, using

    SELECT DISTINCT subdirectory

From a database perspective, you cannot get distinct lists in columns of the same query.

Cheers!
(°v°)
0
 

Author Comment

by:ccnorris
ID: 20082186
What if instead of using distinct  I were to check that entries are unique and just output each instance once?
I can't recall if I've done that before though.  How do I do that?
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question