Grouping Results with CFQUERY and CFOUTPUT
Grouping Results
ColdFusion makes it easy to select data out of a database and display it on a page. As soon as you feel good about your success in crossing the divide from static to dynamic, someone will say "That's nice, can you group the list by author?". Whether you are asked to group things by author, make, brand, year or whatever, ColdFusion has you covered with its grouping capabilities.
To present things in groups, we first rely on SQL to group the data before it is presented to ColdFusion. This is done with a GROUP BY or ORDER BY clause.
| CLAUSE | QUERY |
| GROUP BY | SELECT Author, Title, Publisher GROUP BY Publisher, Author, Title |
| ORDER BY | SELECT Author, Title, Publisher ORDER BY Publisher, Author, Title |
ColdFusion will then display grouped data by using nested CFOUTPUT tags. In both cases the outter most CFOUTPUT will indicate the QUERY and each CFOUTPUT that is used to group will use a GROUP parameter except the innermost CFOUTPUT. The code below will group authors by publisher and then group books by author.
| CODE |
| <CFOUTPUT Query="myquery" GROUP="Publisher"> <p>#Publisher# <CFOUTPUT GROUP="Author"> <br>#Author# <CFOUTPUT> <br>~ #Title# </CFOUTPUT> </CFOUTPUT> </p> </CFOUTPUT> |
| OUTPUT |
Macromedia Press ~Reality ColdFusion MX Newbie Press |
If you use a GROUP BY clause with a WHERE clause the GROUP BY clause most come after the WHERE clause. Think of this as the SQL engine finding all of the results that match the WHERE statement and then it groups the results.
If you use a GROUP BY clause with an ORDER BY, use the GROUP BY before the ORDER BY clause. The SQL engine will create the groups and then order the groups, this reduces the amount of ordering that is done by SQL since the number of groups is the same or less than the number of members of the groups.
There are no comments for this entry.
[Add Comment]