Tips & Tricks: Beginner Database Query Cfoutput

Tips & Tricks: Beginner Database Query Cfoutput Many times on the informational boards, I come across new developers who are working with the concepts of displaying database record sets. They are interested on how to display them in a functional way. I have compiled a short list of different methods here where I will show you some simple solutions to formatting your database query results. I will offer 2 solutions for each sample. The simple record set and the ‘grouped’ record set.

1. Block tags or break tags. This is probably the most familiar method to anyone entering ColdFusion. I have shown both of these examples using the HTML paragraph tags, but you can substitute for any line-breaking code. (<br />, <div></div>. etc)

   Regular record set: <cfoutput query=”[yourqueryname]”> <p>#[columnname]#</p> </cfoutput>

   Grouped record set: <cfoutput query=”[yourqueryname]”> #[sectionname]#<br> <cfoutput> <p>#[columnname]#</p> </cfoutput> </cfoutput>

2. Tables - Horizontal. After working with HTML for a while you will recognize this code as a simple list/report layout.

   Regular record set: <table> <cfoutput query=”[yourqueryname]”> <tr> <td>#[columnname]#</td> </tr> </cfoutput> </table>

   Grouped record set: <table> <cfoutput query=”[yourqueryname]”> <tr> <th>#[sectionname]#</th> </tr> <cfoutput> <tr> <td>#[columnname]#</td> </tr> </cfoutput> </cfoutput> </table>

3. Tables - Vertical. This is similar to the system above with the addition of a ‘counter’. What you do is decide how many vertical results you want to display before ‘breaking’ to the next line of results. I have used 4 results, but choose what you need. I have moved the ‘<tr>’ to the outside of the cfoutputs and have added a to determine if the code should insert a table row termination and start tag. I had previously left out the code that I am adding using this editing color. I had left it out intentionally to keep the 'concept' of the process simple and clear for the new developer. It has been brought to my attention that it can end in badly formed HTML (this was the least of my concerns).

   Regular record set: <table> <tr> <cfoutput query=”[yourqueryname]”> <td>#[columnname]#</td> <cfif currentrow mod 4 eq 0 and currentrow neq recordcount></tr><tr></cfif> </cfoutput> </tr> </table>

   In the grouped example, I have added an additional cfif to check to see if the loop ‘did not’ end on a multiple of 4 AND it is not the very first record in the query. I have also added a variable that carries the row count of every breaking <tr> in the data displays so that it can be checked against the currentrow in the 'group' and not print two recurring breaking <tr> groups together.

   Grouped record set: <table> <tr> <cfoutput query=”[yourqueryname]”> <cfif currentrow mod 4 neq 0 and currentrow neq 1 and currentrow neq lastbreakrow ></tr><tr></cfif> <th colspan=”4”>#[sectionname]#</th> </tr> <tr> <cfoutput> <td>#[columnname]#</td> <cfif currentrow mod 4 eq 0> </tr><tr></cfif> </cfoutput> </cfoutput> </tr> </table>

4. Tables – Horizontal AND Vertical (Newspaper). This is similar to the system above however instead of running the records left to right, we will run them top to bottom for ½ the records and then start at the top again for the 2nd half. (you can also do this for thirds, fourths, fifths, etc).

   Regular record set: <table> <tr> <td> <cfoutput query=”[yourqueryname]”> #[columnname]# <cfif currentrow eq round(recordcount / 2)></td></tr><tr><td><cfelse><br /></cfif> </cfoutput> </td> </tr> </table>

   Grouped record set: <table> <tr> <td> <cfoutput query=”[yourqueryname]”> <p>#sectionname]#</p> <cfoutput> <p>#[columnname]#</p> <cfif currentrow eq round(recordcount / 2)></td></tr><tr><td></cfif> </cfoutput> </tr> </cfoutput> </table>

This is certainly not the only methods available, nor will they be the best formatted versions for your needs, but they should hopefully help you get pointed in the right direction to have your data displayed the way you wanted it.