CFtips.net

QueryAddColumn( )

Filed under ColdFusion , SQL

Last night, I was a working on a problem of trying to diplay entries from a junction table.  The entries in the junction table needed to diplay in one row depending on ID types.   I wasn't sure of the best way to accomplish this, but I discovered the function QueryAddColumn().  QueryAddColumn() can be used to add an array as a column in a query object.  I am thinking about writing a custom tag to perform this operation in my application, but I haven't got that far yet.  My sample script below provides an example of how QueryAddColumn works.

My Output:

Team1 | Team2 | Score

Script:

<cffunction name="getOverUnderTicket" access="public" returntype="query" hint="diplays user entries for over/under">
        <cfargument name="mytnum" type="numeric" default="-1"/>
        <!---set an array for 1st team over under--->
        <cfset var namesOdd = ArrayNew(1)/>
           <!---set an array for 2nd team over under--->
        <cfset var namesEven = ArrayNew(1)/>
        <!---declare local variables for arrays--->
        <cfset var j = 1>
        <cfset var i = 1>
        <cfset var k = 1>
        <!---query to get team pairs for over under--->
        <cfquery name="qryOverUnder" datasource="#db#">
            SELECT            c.tmTeamName, b.ttJoinID, b.ttOverUnderID
            FROM            tblOverUnder a
            INNER JOIN        tblTwoTeams b
            ON                a.ouID = b.ttOverUnderID
            INNER JOIN        tblTeams c
            ON                c.tmID = b.ttTeamID
            WHERE            a.ouTicketID = #mytnum#   
        </cfquery>
        <!---loop over query and add teams to two different arrays--->
        <cfloop query="qryOverUnder">
        <!---if the column is odd store team 1 in array for each over under--->
        <cfif (#j# mod 2) IS 1>
            <cfset namesOdd[k] = "#qryOverUnder.tmTeamName#">
            <cfset k = k + 1>
        <!---if the column is even store team 2 in array for each over under--->
        <cfelseif (#j# mod 2) IS 0>
            <cfset namesEven[i] = "#qryOverUnder.tmTeamName#">
            <cfset i = i + 1>
        </cfif>
        <cfset j = j + 1>
        </cfloop>
        <!---query the over/under for each overunder game on ticket--->
        <cfquery name="qryOneScore" datasource="#db#">
            SELECT         ouScore, ouOverUnder
            FROM        tblOverUnder
            WHERE        ouTicketID = #mytnum#
        </cfquery>
        <!---add two teams into separate columns for grouping over unders--->
        <cfset nColumnNumber2 = QueryAddColumn(qryOneScore,"team1","VarChar",namesOdd)>
        <cfset nColumnNumber3 = QueryAddColumn(qryOneScore,"team2","VarChar",namesEven)>
        <cfreturn qryOneScore>
</cffunction>