SQL/Coldfusion - Get and Display Full Hierarchical Tree Listing

Question

As an example I have a table like this:

folder_id | parent_folder_id | folder_name
------------------------------------------
1         | null             | Main
2         | null             | Departments
3         | null             | Archived
4         | 2                | IT
5         | 2                | Sales
6         | 4                | Error Logs
7         | 6                | 2012

etc...

I need to run a query (if multiple thats ok too) to be able to loop through and display them properly via Coldfusion.

I need it to display like this:

• Main
• Departments
  • IT
    • Error Logs
      • 2012
  • Sales
• Archived

...etc... Going as deep as a user creates them.

I'm just having a hard time trying to understand the logic of how this is going to work. Thanks for any help!

Answer 1

For anyone looking for the answer, I took the code from Cody's link and made it work for me (since it wasn't working quite properly). Thanks Cody and Ciaran! Here it is:

<cfquery name="get_folders" datasource="#application.dsn#">
    select folder_id, parent_folder_id, folder_name
    from folders
    order by folder_name
</cfquery>

<!--- Read all roots (no parent ID) --->
<cfquery name="get_parent_folders" dbtype="query">
    select folder_id, folder_name
    from get_folders
    where parent_folder_id is null
</cfquery>

<ul class="tree">
    <cfloop query="get_parent_folders">
        <cfset processTreeNode(folderId=get_parent_folders.folderId, folderName=get_parent_folders.folder_name) />
    </cfloop>
</ul>

<cffunction name="processTreeNode" output="true">
    <cfargument name="folderId" type="numeric" />
    <cfargument name="folderName" type="string" />
    <!--- Check for any nodes that have *this* node as a parent --->
    <cfquery name="LOCAL.qFindChildren" dbtype="query">
        select folder_id, folder_name
        from get_folders
        where parent_folder_id = <cfqueryparam value="#arguments.folderId#" cfsqltype="cf_sql_integer" />
    </cfquery>
    <li>#arguments.folderName#
        <cfif LOCAL.qFindChildren.recordcount>
            <!--- We have another list! --->
            <ul>
                <!--- We have children, so process these first --->
                <cfloop query="LOCAL.qFindChildren">
                    <!--- Recursively call function --->
                    <cfset processTreeNode(folderId=LOCAL.qFindChildren.folder_id, folderName=LOCAL.qFindChildren.folder_name) />
                </cfloop>
            </ul>
        </cfif>
    </li>
</cffunction>