Query+Array

[incubusm]

Is there any way to store query resultS into an array so we could loop through it one by one.

[mxstu]

You can access query objects like an array. For example

<!--- not tested --->
<cfoutput query="yourQuery">
#yourQuery["someColumn"][currentRow]#
</cfoutput>

[Dan Bracuk]

If you loop through the query, you don't have to worry about arrays.

[RobBurn]

Becareful using <cfoutput query="yourQuery"> I find it to be funky... lol. i do
use it though.... I like the <cfloop query=queryname> to store variables and
use them later as you cannot nest cfloop's or cfoutput - well it will let you
nest cfloops but the answer will be incorrect as it will only give you the 1st
variable in the top cfloop and not index through them. An example i think you
are asking about would be as follows.


<cfquery name="qTasks2" dbtype="query">
SELECT DISTINCT cd, ea, PROJECT_NICKNAME,pm
FROM qTasks
ORDER BY cd,ea
</cfquery>

'arrays declared here

<cfset acd=arraynew(1)>
<cfset pm_name=arraynew(1)>
<cfset aea=arraynew(1)>

<cfif #qTasks.RecordCount# is 0>
<h4>No EA with activities planned within request range</h4>
<cfabort>
</cfif>
<cfset i = 0>
<cfloop query="qTasks2">
<cfset i = i+1 >
'arrays defined here
<cfoutput>
<cfset acd=#cd#>
<cfset aea=#ea#>
<cfset pm_name=#pm#>
</cfoutput>
</cfloop>

Then later I use it - within another query and yet that query is stored
inside another set of arrays .. 2 diminsion in this case
..

<cfloop index="i" from="1" to ="#qTasks2.recordcount#">
<cfquery name="qWBS" dbtype="query">
SELECT distinct cd, ea, startdate, enddate, wbslevel
FROM qTasks
WHERE cd='#acd#' AND ea='#aea#'
</cfquery>
<cfset j = 0>
<!--- <cfdump var="#qWBS#"> --->
<cfoutput query="qWBS">
<cfset j = j+1 >
<cfset awbslevel[j]=#wbslevel#>
<cfset astartdate[j]=#startdate#>
<cfset aenddate[j]=#enddate#>
</cfoutput>
<cfset taskwbscount=j>
</cfloop>


bottom line is if your going to do anymore very simple checks then you can use
cfloop... with the query variables
from it, but any deeper like nesting then define the arrays and use them....
with that said, if you can accomplish this
with database queries then let it do it as it's more powerful than using cfml
code... server memory etc etc..

rob

[mxstu]

>well it will let you nest cfloops but the answer will be incorrect as it will
only give you

>the 1st variable in the top cfloop and not index through them

As you said, you can use nested loops, you just need to use a from/to loop
instead of < cfloop query="..." >. However, since you can already access query
values using array syntax:

yourQuery[colName][rowNumber]

... a separate array is often unnecessary and can even be a waste of memory,
if the goal can be accomplished using an existing query.

You might want to respost your example using the "attach code" option. The
code displayed in the html of your post may not be exactly as you posted it.
The forums interpret [ i ] (with no spaces in between the brackets) as the
symbol for italic text :-)

On a side note, there are some unecessary pound "#" signs in the code. As per
recommendations, pound signs should not be used here:

<cfif #qTasks.RecordCount# is 0>
<cfset awbslevel[j]=#wbslevel#>


... instead use ...


<cfif qTasks.RecordCount is 0>
<cfset awbslevel[j]= wbslevel >