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

Gereld

Answer 1

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)

Blastocyst

How would I structure the loop like your example? – Gereld

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

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

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

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. ;) – Blastocyst