cfloop and query of queries

Question

I have a scenario like below on a page. I have a query being returned from a cfc all_data which has columns, section, state, data. Now the page design looks something like below.

section1 section2 section3 section4 -> select one section to select the state in it

Lets say section1 is selected -> State1 state2 state3 associated with that section need to be displayed -> select one state to see the data related to it.

Say State3 is selected -> related State3 data is shown.

So basically I need 3 cfloops which will accomplish the above. I am doing `

<cfquery name="section" dbtype="query">
 select distinct section from all_data 
</cfquery>`

for first loop, I loop over 'section' query to display all the sections.

<cfloop query ="section">
    <cfquery name="state" dbtype="query">
    select distinct state from all_data where section = section.section
    </cfquery>
</cfloop>

for state display I loop like above. For loop 3 that is the data display I have tried multiple things but nothing seems to work the right way. Is this the right approach. Any insights are appreciated.

The first thing that jumps to my mind is the group attribute of cfoutput. — Dan Bracuk, Jun 07 '17 at 16:08
Why return all the data for all states if you only need one of them? The description of the desired result is unclear. Can you please provide a data sample and mock up of the expected result? — Leigh, Jun 07 '17 at 17:42
@user747291 I think you want to display only Sections list first, and after selecting any one Section, you want to display the corresponding States list of that section, and after selecting a State, you want to display the corresponding Data of that State. Is that you want ? — Rajesh Manilal, Jun 08 '17 at 11:47
IF yes, Where you want to display those sections and states ? In Select box or li or any other HTML element ? — Rajesh Manilal, Jun 08 '17 at 13:12

score 1 · Answer 1 · answered Jun 07 '17 at 15:54

do you mean something like this?

    <cfloop query ="section">
        <cfquery name="state" dbtype="query">
          select distinct state from all_data where section = section.section;
        </cfquery>
        <cfloop query ="state">
            <cfquery name="getdata" dbtype="query">
              select * from all_data where section = section.section 
              and state = state.state;
            </cfquery>
            <cfdump var=#getdata#>
        </cfloop>
    </cfloop>

Thanks all for your inputs – user747291 Jun 12 '17 at 20:25 — user747291, Jun 12 '17 at 20:25

score 1 · Accepted Answer · answered Jun 07 '17 at 17:21

I think you can use the group attribute as below

<cfset myQuery = QueryNew("Section, State, Data", "VarChar, VarChar, VarChar")> 

<cfset newRow = QueryAddRow(MyQuery, 5)> 

<!--- Set the values of the cells in the query ---> 
<cfset temp = QuerySetCell(myQuery, "Section", "Section 1", 1)> 
<cfset temp = QuerySetCell(myQuery, "State", "State 1", 1)> 
<cfset temp = QuerySetCell(myQuery, "Data", "Data 1", 1)> 
<cfset temp = QuerySetCell(myQuery, "Section", "Section 1", 2)> 
<cfset temp = QuerySetCell(myQuery, "State", "State 2", 2)> 
<cfset temp = QuerySetCell(myQuery, "Data", "Data 2", 2)> 
<cfset temp = QuerySetCell(myQuery, "Section", "Section 1", 3)> 
<cfset temp = QuerySetCell(myQuery, "State", "State 2", 3)> 
<cfset temp = QuerySetCell(myQuery, "Data", "Data 3", 3)> 
<cfset temp = QuerySetCell(myQuery, "Section", "Section 2", 4)> 
<cfset temp = QuerySetCell(myQuery, "State", "State 2", 4)> 
<cfset temp = QuerySetCell(myQuery, "Data", "Data 2", 4)> 
<cfset temp = QuerySetCell(myQuery, "Section", "Section 2", 5)> 
<cfset temp = QuerySetCell(myQuery, "State", "State 2", 5)> 
<cfset temp = QuerySetCell(myQuery, "Data", "Data 3", 5)> 

 <cfoutput query ="myQuery" group="Section">
    </br>#Section# <!--- You will get distinct Sections here --->
    <cfoutput group="Section">
            </br>#State#,
            <cfoutput>#Data#,</cfoutput>
    </cfoutput>
 </cfoutput>

May as well get rid of `temp`. No point capturing the result if it is not used. Also, CF10+ introduced a simpler way to create and populate a manual query. See [`QueryNew(columnlist [, columntypelist[, rowData]])`](https://helpx.adobe.com/coldfusion/cfml-reference/coldfusion-functions/functions-m-r/querynew.html). — Leigh, Jun 07 '17 at 17:45

cfloop and query of queries

2 Answers2