Nested cfoutput in a table structure

[don2241]

Hi!
I have two queries that I want to display in different rows in a table.

EX.
<table border=&quot;1&quot;>
<cfoutput query=&quot;qryGetHeading&quot;>
<tr>
<td>#heading_name#</td>
</tr>
<tr>
<td>
<table>
<tr>
<td>I WANT TO PUT A SUBHEADING HERE FROM ANOTHER CFOUTPUT QUERY FUNTION</td>
</tr>
</table>
</td>
</tr>
</cfoutput>
</table>

Thanks
/M.

 

[r937]

if the outer cfoutput's query returns more than one heading, then the inner cfoutput's query will be repeated

perhaps you can run one query for both headings and details and then use the GROUP= parameter?

is there any relationship between the two queries?


rudy

 

[don2241]

Hi!

Yes, both outputs has multiy output and they have a relationship. The outer output has a FK in the inner output DB table.

/M.

 

[csteinhilber]

If there's a relationship between the queries... then you should probably do a join in the select, so they come out in a single, related result set.

Otherwise, you're going to get something like this:

query1_result1
         query2_result1
         query2_result2
         query2_result3
         query2_result4
query1_result2
         query2_result1
         query2_result2
         query2_result3
         query2_result4
query1_result3
         query2_result1
         query2_result2
         query2_result3
         query2_result4
query1_result4
         query2_result1
         query2_result2
         query2_result3
         query2_result4

which it doesn't sound like you want.
But I'm unsure what you mean by a &quot;FK&quot;.
Hope it helps,
-Carl

 

[don2241]

FK refers to a foreign key.

 

[r937]

if there's a relationship between table1 and tablen where the foreign key tablen.table1id in tablen relates to the primary key table1.id in table1, then you could probably do it like this

<cfquery name=&quot;single&quot;>
 select table1.pk1, table1.name1
      , tablen.pkn, tablen.namen
   from table1 
      , tablen 
  where table1.id = tablen.table1id
</cfquery>

<table>
 <cfoutput query=&quot;single&quot; group=&quot;pk1&quot;>
  <tr><th>table1: #pk1# #name1#</th></tr>
  <cfoutput>
  <tr><td>tablen: #pkn# #namen#</th></tr>
  </cfoutput>
 </cfoutput>
</table>

 

[don2241]

To: r937

Yea, that will work!
But it throws an error for the second <cfoutput>
I think its because the my query is returning many different headings, subheading and topics

My query returns:

1 Heading Sub heading1 Topic
1 Heading Sub heading2 Topic
2 Heading2 Sub heading1 Topic
2 Heading2 Sub heading2 Topic
...

What do you think
/M.

 

[r937]

i think i would like to see your query

 

[don2241]

OK here it is

SELECT h.heading_ID, h.heading_name, s.subheading_name, l.title
FROMS map_heading h, Smap_subheading s, Smap_links l
WHERE h.heading_ID = s.heading_ID AND
s.subheading_ID = l.subheading_ID
GROUP BY h.heading_ID, h.heading_name, s.subheading_name, l.title

 

[mrjts]

The problem is that you don't need the second <cfoutput>. The outter <cfoutput query=...> tag will allow you to use #variable# anywhere between them. If you want to output multiple queries inside a cfoutput, use <cfloop query=...>

 

[r937]

<table>
 <cfoutput query=&quot;don2241&quot; group=&quot;heading_ID&quot;>
  <tr><th>#heading_ID# #h.heading_name#</th></tr>
  <cfoutput>
  <tr><td>#subheading_name# #l.title#</th></tr>
  </cfoutput>
 </cfoutput>
</table>

 

[r937]

aargh, too fast with the cut and paste, me

obviously, no table name qualifiers in cf variables

also, let's skip the html table layout so it becomes clearer how the GROUP= parameter works

<cfoutput query=&quot;don2241&quot; group=&quot;heading_ID&quot;>
#heading_ID# #heading_name#
<cfoutput>
#subheading_name# #title#
</cfoutput>
</cfoutput>

 

[don2241]

Hi!
To: r937
If I use your code example it throws a:
java.lang.ArrayIndexOutOfBoundsException

What I am trying to produce is something that looks like this (don't care about the indent)
Ex.
Heading
Subsubheading
Topic

A heading has 1 or many subheadings.
A subheading has 1 or many Topics
Topics is going to be a link to a page for that topic.
It can be seen as a dynamic sitemap if you like

Thx
/M.

 

[don2241]

ooopps!

Each topic actually has 1 or many links

Ex. (again)

Heading
Subheading
Topic
link 1
link 2
link 3
Heading 2
Subheading 1
Topic
link 1
link 2
Subheading 2
Topic
link 1
link 2

Is this structure clear?

Thx
/M.

 

[r937]

yeah, the structure is clear

<cfoutput group=&quot;heading&quot;>
#heading#
<cfoutput group=&quot;subheading&quot;>
#subheading#
<cfoutput group=&quot;topic&quot;>
#topic#
<cfoutput>#link#</cfoutput>
</cfoutput>
</cfoutput>
</cfoutput>

nothing wrong with nesting cfoutputs 4 deep like that

make sure you have an ORDER BY in that order

the java error is your problem, i only know cf

if you're still getting it, show your query and cfoutputs again


rudy

 

[don2241]

It only displays the first row returned by the query. My query is returning 70 rows.

QUERY
SELECT h.heading_ID, h.heading_name, s.subheading_name, l.title, l.url
FROM Smap_heading h, Smap_subheading s, Smap_links l
WHERE h.heading_ID = s.heading_ID AND
s.subheading_ID = l.subheading_ID
GROUP BY h.heading_ID, h.heading_name, s.subheading_name, l.title, l.url

CF CODE
<table border=&quot;1&quot;>
<cfoutput query=&quot;qryGetAll&quot; group=&quot;h.heading_ID&quot;>
<tr>
<td>#heading_name#</td>
</tr>
<cfoutput group=&quot;s.subheading_name&quot;>
<tr>
<td>#subheading_name#</td>
</tr>
<cfoutput group=&quot;l.title&quot;>
<tr>
<td><a href=&quot;#url#&quot;>#title#</a></td>
</tr>
</cfoutput>
</cfoutput>
</cfoutput>
</table>

 

[r937]

what happened to topic, the third level of grouping?

[tt]select h.heading_ID, h.heading_name
, s.subheading_name
, l.title, l.url
from Smap_heading h
, Smap_subheading s, Smap_links l
where h.heading_ID = s.heading_ID
and s.subheading_ID = l.subheading_ID
order
by h.heading_ID, h.heading_name
, s.subheading_name
, l.title, l.url

table border=&quot;1&quot;>
<cfoutput query=&quot;qryGetAll&quot; group=&quot;heading_ID&quot;>
<tr>
<td>#heading_name#</td>
</tr>
<cfoutput group=&quot;subheading_name&quot;>
<tr>
<td>#subheading_name#</td>
</tr>
<cfoutput>
<tr>
<td><a href=&quot;#url#&quot;>#title#</a></td>
</tr>
</cfoutput>
</cfoutput>
</cfoutput>
</table>[/tt]

 

[don2241]

Hi!
Thank you for all the help. I finally got it to work this is how I did it: (Title is actually the topic...sorry)

The include template is a function that takes a list and simply deletes all duplicates.


<cfinclude template=&quot;/udf/UDFunctions.cfm&quot;>
<cfset tmp = ListDeleteDuplicates(valuelist(qryGetAll.Heading_name))>
<cfset tmp2 = ListDeleteDuplicates(valuelist(qryGetAll.Heading_ID))>

<table border=&quot;1&quot;>
<cfloop from=&quot;1&quot; to=&quot;#listlen(tmp)#&quot; index=&quot;i&quot;>
<tr>
<td><cfoutput>#listgetat(tmp, i)#</cfoutput></td>
</tr>
<cfoutput query=&quot;qryGetAll&quot; group=&quot;subheading_name&quot;>
<cfif listgetat(tmp2, i) eq qryGetAll.heading_ID>
<tr>
<td>#subheading_name#</td>
</tr>
<cfoutput>
<tr>
<td><a href=&quot;#url#&quot;>#title#</a></td>
</tr>
</cfoutput>
</cfif>
</cfoutput>
</cfloop>
</table>