Using nested cfloop queries

Question

I have 2 tables. One is for categories, the second is for Questions.

 category table: 

 category_id
 category_name

 questions table:

 question_name
 question_id
 category_id

How can I loop though all the category names and show the questions grouped under each category name? Using ColdFusion, so I am assume I should use <CFLOOP>

The results should look something like this.

Category1

Question 1
Question 2

Category2

Question 4
Question 5

Peter Boughton · Accepted Answer · 2013-05-03 18:11:26Z

11

If you loop with cfoutput, you can group by a specific column, and then have an inner loop for items in that column.

Like this:

<cfquery name="Questions">
    SELECT q.question_name , c.category_name

    FROM questions q
    JOIN category c
        ON c.category_id = q.category_id

    ORDER BY c.category_name , q.question_name
</cfquery>

<cfoutput query="Questions" groupby="category_name">
    **#category_name#**
    <cfoutput>
        #question_name#
    </cfoutput>
</cfoutput>

~~Annoyingly, this grouping feature hasn't been added to the main cfloop, you need to use it via cfoutput. :(~~

Update: In ColdFusion 10 and Railo 4, you can now do this with cfloop rather than cfoutput. Note however that the attribute is group not groupby:

<cfloop query="Questions" group="category_name">
    **#category_name#**
    <cfloop>
        #question_name#
    </cfloop>
</cfloop>

Important: If this is HTML output, use HtmlEditFormat(question_name) to avoid potential HTML injection. Similarly, JsStringFormat(question_name) to avoid JS injection, etc.

Again, both CF10/R4 have improved this sitution too, with more consitently named encodeForX methods (i.e. encodeForHtml, encodeForJavaScript, etc)

edited May 3, 2013 at 18:11

answered Jun 3, 2010 at 23:58

Peter Boughton

113k32 gold badges125 silver badges177 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

jeff Over a year ago

How would I structure the loop like your example?

Henry Over a year ago

@jeff, what do u mean? cfoutput already loop over the query for you.

Henry Over a year ago

btw, don't forget to use htmlEditFormat() in between the ##. :)

jeff Over a year ago

Sorry, have it now. Was confused by the nested cfoutput tags. Thank you very much Peter. Thank Henry as well.

Peter Boughton Over a year ago

Yeah, your confusion is exactly why it'd be better using cfloop tags. (If the Advisory Committee was getting anywhere I'd try asking for it to be added to core.) Henry - good point, must escape output - although no guarantee this is HTML. ;)

Collectives™ on Stack Overflow

Using nested cfloop queries

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related