Toby,
Their are two ways that you could do this. First way it to return your
data in a column based manor (the "way you want it") then just print it
out, the second is to return it in a row based manor ( the "way it is
now"). Each way has issues.
Column Based : What it seems like you would like to be able to pivot
your data so that the X direction is Y and vice versa. In Access you
can use the SQL TRANSFORM function to take data and pivot it across an
axis. However in SQL server (and I believe Oracle) you don't get such a
function (I've been trying to figure out how that is for quite a while).
If a TRANSFORM or similar function is not available to you, then you can
use SQL CASE statements to artificially create this pivot in the data.
The problem with this approach is that its not very flexible. If your
options are changing frequently or vary greatly this method is probably
going to cause you more headache than its worth.
Row Based : The way you got the data back looks great to me. Sure you
are getting back a bit of redundant data, but you are going to put much
less of a load on the DB than approaching this in a column based manor.
Have you ever used the GROUP attribute of CF Output? This is exactly
the situation its for. To pull this off (easily) I recommend you do two
things .... 1) Return the data using some *outer* joins so that their is
a row for every option for every poem (see SQL below) 2) Use CFOUTPUT
with the GROUP tag. I could see it working something like this, keep in
mind the code below is a skeleton I'm sure you'll need some other logic.
DISCLAIMER : I didn't test any of this code ...so be prepared to fiddle
with it (sorry if eMail messes up the formatting)
<cfquery name="getPoems" datasource="blah">
SELECT p.poem_id, p.poem_title,
po.setting,
pod.option_id, pod.option_name
FROM poem p
LEFT OUTER JOIN
poem_option po ON p.poem_id = po.poem_id
LEFT OUTER JOIN
poem_option_def pod ON po.option_id pod.option_name
ORDER BY p.poem_id, option_name
</cfquery>
<table>
<!---
You'll want to do another loop before this one to
print out a header row for the table just loop over the
query where printing out "option_name" until you see the
poem_id change ...then close the TR and move one
--->
<cfoutput query="getPoems" group="poem_id">
<tr>
<td>#getPoems.poem_id#</td>
<cfoutput>
<td>#getPoems.option_name#</td>
</cfoutput>
</tr>
</cfoutput>
</table>
I hope this helps.
-eric
------------------------------------------------
Common sense is genius dressed in its working clothes.
-- Ralph Waldo Emerson
Eric Barr
Zeff Design
(p) 212.714.6390
(f) 212.580.7181
-----Original Message-----
From: Toby Tremayne [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, March 06, 2001 10:47 PM
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
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
