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?
drstueyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Servers

From novice to tech pro — start learning today.