thanks for all the help guys - Nick your outputting cf code gave me an idea.
I was working on stored procs and outer joins and grouping and all kinds of
stuff.. in the end this is how I did it:
<cfquery name="getAuthorPage" datasource="#request.maindsn#">
select p.poem_title,
p.poem_id,
p.date_written,
p.submission_type,
p.poem_blurb,
p.poem_content,
p.author_id,
pod.option_name,
po.setting
from member m, poem p, poem_option po, poem_option_def pod
where m.member_id = p.author_id
and m.member_id = #attributes.member_id#
and po.poem_id = p.poem_id
and pod.option_id = po.option_id
and p.poem_id in (select p.poem_id
from poem p, poem_option po, poem_option_def
pod
where p.poem_id = po.poem_id
and po.option_id = pod.option_id
and po.setting = 'off'
and pod.option_name = 'hidden')
order by p.date_submitted
</cfquery>
<cfset current_id = "">
<cfset options = "">
<cfoutput query="getauthorPage">
<cfset options = listAppend(options,"#option_name#=#setting#")>
<cfif listlen(options) is getPoemOptionDefs.recordcount>
<cfif listcontains(options,"hilite=on")>
<tr>
<td width="20%"> </td>
<td>
<img src="#request.imgRoot#/common/arrowright.gif" border="0">
<a
href="index.cfm?fuseaction=#attributes.XFA.browseAuthorList#&member_id=#memb
er_id#&startNum=#currentRow#&#urltoken#"
class="grey">
#poem_title#
</a>
<img src="#request.imgRoot#/common/arrowleft.gif" border="0">
</td>
</tr>
<cfelse>
<tr>
<td width="20%"> </td>
<td>
<a
href="index.cfm?fuseaction=#attributes.XFA.browseAuthorList#&member_id=#memb
er_id#&startNum=#currentRow#&#urltoken#"
class="grey">
#poem_title#
</a>
</td>
</tr>
</cfif>
<cfset options = "">
</cfif>
</cfoutput>
ridiculously simple for something that's been driving me crazy. It just
goes to prove once again that when you have a complex problem, break it down
into simple elements - determine exactly what you want to do in the simplest
terms possible, and work from there. I got stuck on the idea that I needed
to do some exotic query or stored procedure and near drove myself crazy(er).
The above code works perfectly, and allows me to code conditionally for any
option I end up having. Plus from a database of around 900 poems and
outputting a list of 70 odd (for the author_id I was testing it on) the
whole pae load took just under 250 milliseconds, which I think i can live
with.
Thanks again for the help guys
Toby Tremayne
Code Poet and Zen Master of the Heavy Sleep
Show Ads Interactive
359 Plummer St
Port Melbourne
VIC 3207
P +61 3 9245 1247
F +61 3 9646 9814
ICQ UIN 13107913
-----Original Message-----
From: DeVoil, Nick [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, 7 March 2001 8:47 PM
To: CF-Talk
Subject: RE: curly queries
Toby
Something like this will get you n rows per poem where n
is the number of options, filtering out the hidden ones.
Assuming each poem must have options - otherwise you need
an outer join.
select p.poem_id, p.poem_title, d.option_name, o.setting
from poem p, poem_option o, poem_option_def d
where p.poem_id = o.poem_id
and d.option_id = o.option_id
and poem_id in
(select p.poem_id from poem p, poem_option o, poem_option_def d
where p.poem_id = o.poem_id
and d.option_id = o.option_id
and d.option_name = 'hidden'
and d.setting = 'off')
You could use <cfoutput group="poem_id"> in CF
to "group" the output, but actually you want everything
on one line, so I think you'll have to do something
along the lines of
<cfset current_id = "">
<cfoutput query=...>
<cfif poem_id is not current_id>
<!--- New poem, so new line & poem id/title--->
<br>
#poem_id# #poem_title#
<cfif>
<!--- option details --->
#option_name#=#setting#
<current_id = #poem_id#
</cfoutput>
I can't think of a way of doing headers like your example,
if you dont want to hardcode the options.
None of this tested I'm afraid ;-)
Nick
-----Original Message-----
From: Toby Tremayne [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, March 07, 2001 3:47 AM
To: CF-Talk
Subject: curly queries
I've got a tricky problem that I just can't figure out.
I'm outputting a list of poems fomr a database (just the titles.) Each poem
has several "options" such as hidden, hilite etc. As I output the titles in
a list, I need to be able to run conditional checks against each option (IE
if hilite is "on" then display markers next to the title.) The tables are
set up like so:
-------------------------
poem
-------------------------
poem_id numeric
poem_title varchar
-------------------------
-------------------------
poem_option
-------------------------
poem_id numeric
option_id numeric
setting varchar
-------------------------
-------------------------
poem_option_def
-------------------------
option_id numeric
option_name varchar
-------------------------
you can see my problem (hopefully). To start with I'm getting each
poem_title repeated once for every option that exists. In the query I need
to select all this information for only the poems whose "hidden" option has
a setting of "off", then I need to output the title only once obviously, at
that time check each of it's options.
This doesn't quite work because while what I NEED is this:
one record:
---------------------------------------------------------------------
poem_id | poem_title | hidden | hilite | spellcheck |
---------------------------------------------------------------------
01 "my poem" "off" "on" "on"
instead, I have this:
one record:
---------------------------------------------------------
poem_id | poem_title | option_name | setting |
---------------------------------------------------------
01 "my poem" "hidden" "off"
01 "my poem" "spellcheck" "on"
01 "my poem" "hilite" "on"
as you can see this makes it difficult to do a <cfif hilite is "on"> <img
src="hiliter.gif"> </cfif>
can anyone help me with this please? i know it's going to be a combination
of writing the correct query and grouping the output - it's just eluding me
utterly. I can't / don't want to hardcode each option into the queries etc,
because obivously the number and names of options will evnetually grow...
help - anyone?
Toby Tremayne
Code Poet and Zen Master of the Heavy Sleep
Show Ads Interactive
359 Plummer St
Port Melbourne
VIC 3207
P +61 3 9245 1247
F +61 3 9646 9814
ICQ UIN 13107913
**********************************************************************
Information in this email is confidential and may be privileged.
It is intended for the addressee only. If you have received it in error,
please notify the sender immediately and delete it from your system.
You should not otherwise copy it, retransmit it or use or disclose its
contents to anyone.
Thank you for your co-operation.
**********************************************************************
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
