Outputting Query Results’ ColdFusion Help

Now that you have learned various ways to retrieve data from a database, you need to learn how to output the data in your ColdFusion template. To do this you will be using the CFOUTPUT tag. The CFOUTPUT tag has several optional attributes.

In order to display query results, you must add the QUERY attribute to the CFOUTPUT tag. The name that you’ gave to your query in the CFQUERY tag is what should be entered for the QUERY attribute of the CFOUTPUT tag. Look at the example demonstrated in Listing 10.6 and the results

For each record returned by the query, ColdFusion will loop through the code between the opening and closing CFOUTPUT tags. In Figure 10.5, three speakers werereturned, so the code was executed’three times,  You can also pull data from two tables in one query. ‘often, when this is-done, there will be one record returned tt6m one table that is tied to multiplerecords returned in a seconCl”table. In this scenario, you would need to group your results so that duplicate’ , Information is not displayed. The following tables demonstrate results from an ungrouped recordset and a grouped recordset. If you- do not group your output, you will get results similar tothat in Table 10.4, However, if you add grouping, you can . organize related iriformation into groups

In the next example, you will work with a query that pulls data from two tables in a one-to-many relationship. A one-to-many relationship means that one table has one. record that is tied to multiple records in a second table through a join on a particular field (usually the primary and foreign keys). Figure 10.2 is a visual example of a one-tomany relationship between two tables. For each record in the speakers table, there are 15 records tied to it in the speaker_books table through the field speaker _id.

Normally, when the results are returned, you receive repetitive information for each row (see Figure 10.6). Displaying grouped results via Coldfusiorrgrouping allows you to present various levels of information that suppress the repetitive data.. . In order to group your query results using the ColdFusion grouping method (as opposed to using GROUP BYin your SQL statement), you need to make sure that the fields you wish to group on are in your SQL ORDER BYclause in the orderthat they .If should be grouped. Then, when you enter your CFOUTPUT tag, add the QUERY and GROUP
attributes. The GROUP attribute should be set to the field you would like to group by (the one that was listed in your ORDER BYclause).

Then, once you have displayed your first level of grouping, you will need to nest a seeond- CFOUTPUTtag to surround your second level of grouping. No attributes should be added Unless you plan on grouping three levels deep. In that case you would need to add the GROUPattribute in the second CFOUTPUTtag set to the second-level grouping field listed in your ORDER.BYclause. The last nested CFOUTPUTtag should not have a ~ROUPattribute. Remember to close all of your CFOUTPUTtags. Listing 10.7 provides an example of how to do two-level grouping with CFOUTPUTa,nd the results can be seen .

Grouping Output with CFOUTPUT

Posted on November 14, 2015 in Using CFQuery and SQL to Interact with the Database

Share the Story

Back to Top
Share This