• Home
• Tutorials
• Forums
• Reference
• Developers
• Advertise
• About Us

Automatically Query To CFM

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 !!!