5

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
Leigh
  • 28,765
  • 10
  • 55
  • 103
jeff
  • 313
  • 2
  • 4
  • 10

1 Answers1

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)

Peter Boughton
  • 110,170
  • 32
  • 120
  • 176
  • How would I structure the loop like your example? – jeff Jun 04 '10 at 02:08
  • @jeff, what do u mean? cfoutput already loop over the query for you. – Henry Jun 04 '10 at 02:17
  • 1
    btw, don't forget to use htmlEditFormat() in between the ##. :) – Henry Jun 04 '10 at 02:18
  • Sorry, have it now. Was confused by the nested cfoutput tags. Thank you very much Peter. Thank Henry as well. – jeff Jun 04 '10 at 04:52
  • 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. ;) – Peter Boughton Jun 04 '10 at 07:26