QUERYRENDER CUSTOM TAG

QUERYRENDER.CFM


<cfparam name="Attributes.QueryName" default="Query">
<cfparam name=
"Attributes.DataSource" default=""
<cfparam name=
"Attributes.ColumnList" default="*">
<cfparam name=
"Attributes.TableList" default="">
<cfparam name=
"Attributes.Constraints" default="">
<cfparam name=
"Attributes.GroupBy" Default="">
<cfparam name=
"Attributes.OrderBy" Default="#Attributes.ColumnList#">

<pre>
    <!---------------------------------------------------------------------------------------->
   
<font color="red">GENERATE QUERY FROM CUSTOM TAG ATTRIBUTES</font>
    <!---------------------------------------------------------------------------------------->
   
SELECT #Attributes.ColumnList#
    FROM #Attributes.TableList#
    <cfif Attributes.Constraints neq "">WHERE #PreserveSingleQuotes(Attributes.Constraints)#</cfif>
    <cfif Attributes.GroupBy
neq "">GROUP BY #Attributes.GroupBy#</cfif>
    <cfif Attributes.OrderBy
neq "">ORDER BY #Attributes.OrderBy#</cfif>
</pre>

<!--- Generate the following query syntax --->

<cfquery name="#Attributes.QueryName#" datasource="#Attributes.DataSource#">
   
SELECT #Attributes.ColumnList#
    FROM #Attributes.TableList#
    <cfif Attributes.Constraints neq "">
        WHERE #PreserveSingleQuotes(Attributes.Constraints)# 
    </cfif>
    <cfif Attributes.GroupBy
neq "">
        GROUP BY #Attributes.GroupBy#
    </cfif>
    <cfif Attributes.OrderBy
neq "">
        ORDER BY #Attributes.OrderBy# 
    </cfif>
</cfquery>



<pre>
    <!---------------------------------------------------------------------------------------->
    <font color="red">RENDER THIS QUERY</font>
    <!---------------------------------------------------------------------------------------->
</pre>

<!--- Create a list of columns to loop through --->

<cfset LoopList = "#Attributes.QueryName#.ColumnList">

<table Border="1">
    <tr>

        <cfloop index="ColumnHeadings" list="#Evaluate(LoopList)#">
        <th>
            <cfoutput>#ColumnHeadings#</cfoutput>
        </th>
        </cfloop>
    </tr>

    <cfoutput query="#Attributes.QueryName#">
    <tr bgcolor="#iif(CurrentRow mod 2, DE('efefef'), DE('White'))#">
        <cfloop index="lstLp" list="#Evaluate(LoopList)#">
            <td>#Evaluate(LstLp)#</td>
       
</cfloop> 
    </tr>
    </cfoutput>
</table>
<cfdump var="#Variables#">
Save the custom tag above in the Custom Tag directory as QUERYRENDER.CFM

TEST.CFM

I created a page called TEST.CFM to test this custom tab
On the Page where you want to run a query and display result in a table just use the code ?.

<cf_QueryRender
        QueryName=
"NW"
        DataSource=
"NorthWind"
        ColumnList=
"FirstName, LastName"
        TableList=
"Northwind.dbo.Employees"
        Constraints=
""
        GroupBy=
""
        OrderBy=
"">


That's it !!!



Of course there are a few rules that you should follow. In the columnlist part of the tag you can use aliases if you want. For example 
FirstName as FName, LastName as Last_Name
Notice no quotes around the aliases and no spaces either.
You can also write JOIN Statements in the Table list like;
The dump will show with spaces but the table will not be rendered as the loop will not know what to do when is comes across a space.


TABLELIST="Product P Left Join ProductOrder PO on Po.ProductId = P.ProductId"

This tag of course does not give you you an easier way of writing queries. It just renders the rest of the page for you.

If you do not want to display the dump simply take out the <cfdump var="#variables#"> line out of the custom page. 



I found this is really a whole lot easier instead of having to write query tags and then query render blocks over and over again. Let me know if you think of improvements !!!


About This Tutorial
Author: Anang A Phatak
Skill Level: Intermediate 
 
 
 
Platforms Tested: CF5,CFMX
Total Views: 94,489
Submission Date: October 29, 2004
Last Update Date: June 05, 2009
All Tutorials By This Autor: 12
Discuss This Tutorial
  • This custom tag is capable of rendering your query for you. But it will not take spaces in your column_name aliases. Here is a little enhancement idea to trick this tage into outputting spaces in column headings. Inside columnlist use "_" (underscores) for word seperators. So it will look like shown below; and then inside "queryrender.cfm" page use the replace function like so; #ReplaceNoCase(ColumnHeadings, "_", " ", "All")# Now you will have a greate looking table automatically created for you. Hope this helps Anang

Advertisement

Sponsored By...
Mobile App Development (IOS, Android, Cordova, Phonegap, Objective-C, Java) - Austin, Texas Mobile Apps - Touch512, LLC.