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

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)