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?
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.J ournal#
</CFOUTPUT>
</SELECT>
<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#">
</CFOUTPUT>
</SELECT>
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.)
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.J ournal# </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
<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#">
</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
also in the query you need to use to a
select distinct journal