Search code examples
coldfusionnestedcfquery

Nesting queries in CF


I'm using this code to display a list of platforms. If a platformID was specified upon entering the page, I would like to create a list of genres underneath the specified platform.

  1. browse.cfm was accessed via a link that specified a platformID of 1
  2. browse.cfm will list all available platforms
  3. browse.cfm will now list all available genres under platformID of 1.

    <ul>
        <li>Browse</li>
        <cfoutput query="qGetPlatforms">
        <li>
            <a href="browse.cfm?platformID=#URLEncodedFormat(Trim(qGetPlatforms.platformID))#">#qGetPlatforms.pName#</a>
            <cfif URL.platformID EQ qGetPlatforms.platformID>
            <ul>
                <cfoutput query="qGetGenres">
                <li><a href="browse.cfm?genreID=#URLEncodedFormat(Trim(qGetGenres.genreID))#">#qGetGenres.gName#</a></li>
                </cfoutput>
            </ul>
            </cfif>
        </li>
        </cfoutput>
    </ul>
    

By using this approach, however, I'm getting an invalid nesting configuration. How do I fix this? Or is there another approach to achieve the same idea?

Thanks

MY queries:

<!---Get platforms--->
<cffunction
    name="fGetPlatforms"
    access="public"
    returntype="query"
    output="false"
    hint="I get all the platforms">
    <!---Local var--->
    <cfset qGetPlatforms = "">
    <!---Database query--->
    <cfquery name="qGetPlatforms" datasource="#REQUEST.datasource#">
    SELECT 
        platforms.platformID,
        platforms.platformName AS pName
    FROM
        platforms
    </cfquery>
    <cfreturn qGetPlatforms>
</cffunction>    

<!---Get genres--->
<cffunction
    name="fGetGenres"
    access="public"
    returntype="query"
    output="false"
    hint="I get all the genres">
    <!---Local var--->
    <cfset qGetGenres = "">
    <!---Database query--->
    <cfquery name="qGetGenres" datasource="#REQUEST.datasource#">
    SELECT 
        genres.genreID,
        genres.genreName AS gName
    FROM
        genres
    </cfquery>
    <cfreturn qGetGenres>
</cffunction>

Solution

  • You can use <cfloop query="qGetGenres"></cfloop>, they can be nested.

    IMO, using cfoutput for looping over the queries is old style and should be avoided. Use cfoutput for output, cfloop for looping and you'll have more readable code.