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. :(

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