• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 142
  • Last Modified:

only one occurance of each unique record please

My first CF application is progressing. Its a press clippings database BTW.
I'm trying to dynamically fill a pull down menu in a form with all the possible publications, but instead of just one occurance of each journal, there is one for every record in the database. i.e. the form looks like:

...
<OPTION NAME="Journal" VALUE="Melody Maker">Melody Maker
<OPTION NAME="Journal" VALUE="Melody Maker">Melody Maker
<OPTION NAME="Journal" VALUE="Melody Maker">Melody Maker
<OPTION NAME="Journal" VALUE="Melody Maker">Melody Maker
<OPTION NAME="Journal" VALUE="Melody Maker">Melody Maker
<OPTION NAME="Journal" VALUE="Mix-Mag">Mix-Mag
<OPTION NAME="Journal" VALUE="Mix-Mag">Mix-Mag
<OPTION NAME="Journal" VALUE="Mojo">Mojo
<OPTION NAME="Journal" VALUE="New York Times">New York Times
<OPTION NAME="Journal" VALUE="NME">NME
<OPTION NAME="Journal" VALUE="NME">NME
<OPTION NAME="Journal" VALUE="NME">NME
<OPTION NAME="Journal" VALUE="NME">NME
<OPTION NAME="Journal" VALUE="NME">NME
...

the code I used is:

<CFQUERY DATASOURCE="press" NAME="Journal">
SELECT Journal
FROM Refs
ORDER BY Journal
</CFQUERY>

<SELECT NAME="Journal">
<OPTION NAME="All" VALUE="All" selected>All<br>
<CFOUTPUT QUERY="Journal">
  <OPTION NAME="Journal" VALUE="#Journal#">#Journal#
</CFOUTPUT>
</SELECT>

what am I doing wrong?
0
drstuey
Asked:
drstuey
  • 4
1 Solution
 
bigbadbCommented:
you need to refer to the query by

#journal.journal#

for both value and the text outside the option tag

also end the option tag with </option>
0
 
bigbadbCommented:
btw the first journal references the query journal


also in the query you need to use to a

select distinct journal

0
 
bigbadbCommented:
so over all do the following

<CFQUERY DATASOURCE="press" NAME="Journal">
SELECT Distinct Journal
FROM Refs
ORDER BY Journal
</CFQUERY>

<SELECT NAME="Journal">
<OPTION NAME="All" VALUE="All" selected>All<br>
<CFOUTPUT QUERY="Journal">
  <OPTION NAME="Journal" VALUE="#journal.Journal#">#journal.Journal#
</CFOUTPUT>
</SELECT>

0
 
drstueyAuthor Commented:
excellent stuff BigBadB, that works a treat. Points will be coming your way. One other thing though. Occasionally in the database I have added extra info to the journal name e.g.

Observer
Observer Life section
Oberver Review

currently all these possibilities have an option in the form.

Is there a way of only having one entry for all the Observer possibilities?

(BTW I also notice that I have:

Guardian
Guardian 2
Guardian Weekend
The Guardian
The Guardian (G2 section)
The Guardian (tabloid section)

but to solve this I just need to go through the database and make sure I use consistant Journal names each time, so that's my problem.)
0
 
bigbadbCommented:
i suppose you could use the left function



<cfset hold = "">
<SELECT NAME="Journal">
<OPTION NAME="All" VALUE="All" selected>All<br>
<CFOUTPUT QUERY="Journal">

  <cfset holdfirst = left(#journal.journal#, 6)
  <cfif #holdfirst# not eq #hold>
     <OPTION NAME="Journal" VALUE="#journal.Journal#">#journal.Journal# </option>
  </cfif>
<cfset hold = left(#journal.journal#, 6)
</CFOUTPUT>
</SELECT>


this may work for you.  you would have only one occurance of observer

Not sure if this is what you are looking for or not???  let me know if not
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now