Link to home
Start Free TrialLog in
Avatar of drstuey
drstuey

asked on

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?
ASKER CERTIFIED SOLUTION
Avatar of bigbadb
bigbadb

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of bigbadb
bigbadb

btw the first journal references the query journal


also in the query you need to use to a

select distinct journal

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>

Avatar of drstuey

ASKER

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