recordcount of a nested cfoutput ....

[Shaffer]

<cfoutput query='sqlGetAgents' group='areaName'> <h4>#areaName#</h4>
<cfoutput group='agentID'> <p><strong>#agentName#</strong></p> </cfoutput>
</cfoutput> how do i get the recordcount of the number of items within the
nested cfoutput ? thanks

[blewis]

You would have to do some counting yourself. There is no RecordCount for the
nested grouped output, because it is really all the same query, CF is just
doing a little formatting for you. You are going to have to set a variable for
the "areaName" and see if it is different than the previous row...if it is,
start a counter for the inner loop until that areaName changes again.

[OldCFer]

Well, if that's what you really want in the cfoutput then:

Select distinct areaname, agentname from sometable order by areaname

would give you the same results, and RecordCount would be equal to the
nested number of items.

[Shaffer]

ok, Let me see if i can explain a little better: We have a query that gets
countrys and agents that belong to that country...we want to display the
country and the details for each agent that belongs to that country.....we need
to know the number of agents that belong to each country (hope you with me so
far) the sql i have for this is as follows: (btw, using mysql 4.0) <cfquery
name='sqlGetAgents' datasource='#Application.dsn#'> SELECT
agent_areas.agent_area_id, agents.agent_id AS agentID, agents.agent_name AS
agentName, agents.agent_address AS agentAddress, agents.agent_telephone_no AS
agentTelNo, agents.agent_fax_no AS agentFaxNo, agents.agent_email_address AS
agentEmail, agent_areas.agent_area_name AS areaName FROM agents, agent_areas,
agent_regions, agent_divisions, product_divisions WHERE
agent_regions.agent_region_id = #url.id# AND agent_areas.agent_area_id =
agents.agent_area_id AND agent_regions.agent_region_id =
agent_areas.agent_region_id AND agents.agent_id = agent_divisions.agent_id AND
product_divisions.product_division_id = agent_divisions.product_division_id AND
agent_divisions.product_division_id = #url.did# <!--- did = Division ID Passed
from product_detail.cfm ---> ORDER BY agent_areas.agent_area_id DESC,
agents.agent_name </cfquery> and this is the code for the output:
<cfoutput query='sqlGetAgents' group='areaName'> <h4>#areaName#</h4> <cfoutput
group='agentID'> <p><strong>#agentName#</strong></p> </cfoutput> </cfoutput>
in the second cfoutput i want to put something like: <cfif noOfAgents GT 1>Do
this</cfif> <cfif agent.currentRow EQ noOfAgents - 1>do this </cfif> hope that
makes sense i've tried putting COUNT within my query: eg: select
count(agentName) as total from tableName where bla bla bla, but it wont
work....because it will only return the total no of records, not the ones
within the nested cfoutput also tried sql count on group by, but that gets rid
of some results that we need to return... any ideas ? thanks shafiq :sK

[zoeski80]

Hi Shaffer

Attached code shows the count that you need to add to your existing code. Have
also shown an alternate method which will produce the same outcome.

I haven't looked too closely at your query but will it retrieve the areas that
have no agents in them?? It doesn't look like it as you aren't using OUTER
JOINS at all. When joining to the agents table you'd need to do a LEFT OUTER
JOIN to also retrieve the areas that don't have any agents setup.
(NB. alternate code will not have a proble with areas that have not agents
setup)

Hope this helps.

Zoe

<!--- loop over query grouped by area name --->
<cfoutput query="sqlGetAgents" group="areaName">

<h4>#areaName#</h4>

<!--- set/reset agent Counter ---->
<CFSET agentCount = 0>

<cfoutput group="agentID">
<CFSET agentCount = agentCount + 1>
#agentName#
</cfoutput>

<!--- agentCount will be the recordCount for the area --->
<CFIF agentCount GT 1>
Do this
</CFIF>

</cfoutput>


alternative

<!--- get all areas --->
<CFQUERY NAME="getAreas">
select * from agent_areas
</CFQUERY>

<!--- get all agents --->
<CFQUERY NAME="getAllAgents">
select * from agents
</CFQUERY>


<!--- loop over areas --->
<CFLOOP QUERY="getAreas">

<!--- get agents for the current area - using QoQ --->
<CFQUERY NAME="getAreaAgents">
SELECT *
FROM getAllAgents
WHERE Area = #AreaID#
</CFQUERY>

<CFIF getAreaAgents.RecordCount GT 0>
<!---- loop over agents for the area --->
<CFLOOP QUERY="getAreaAgents">
#agentName
</CFLOOP>
<CFELSE>
No Agents for this Area!!
</CFIF>

</CFLOOP>