• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
0

How to output cfquery to array?

Community Beginner ,
Oct 27, 2015 Oct 27, 2015

Copy link to clipboard

Copied

Hello everyone,

I'm trying to output my cfquery to array. I need something like this:

[code]

<cfset myarray=arrayNew(2)>

<cfloop query="qryCustomers">

     <cfset myarray = [{"CustomerID":ID,"Name":FirstLast}]>

</cfloop>

[/code]

I tried this code but that did not work. Error always indicate that something is wrong in my cfset where I have my ID and Customer name together. I'm not sure either if I can do this in cold fusion. I have to put these to values together because I have to use them later to compare with other values from second query. If anyone can tell me what is wrong in my code I would appreciate that. Thanks in advance.

Views

5.9K

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Advocate ,
Oct 27, 2015 Oct 27, 2015

Copy link to clipboard

Copied

One way to do it:
<cfloop query="qryCustomers">
     <cfset myarray[#currentRow#][1] = #ID#>
     <cfset myarray[#currentRow#][2] = #FirstLast#>
</cfloop>
Cheers
Eddie

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Beginner ,
Oct 28, 2015 Oct 28, 2015

Copy link to clipboard

Copied

How I can create a structure first and then add it to an array?

So I tried something like this but still does not work.

[CODE]

<cfset recs = StructNew()>

   <cfloop query="qryTest">

        <cfset recs[currentRow] = StructNew()>

        <cfset recs[currentRow].Customer = ' ' & qryTest.CustomerID>

         <cfset recs[currentRow].Number = ' ' & qryTest.Number>

  </cfloop>

    

  <cfset records = [{CustomerID = "#CustomerID#",Number = "#Number#"}] />

  <cfset fncResults.data = recs>

[/CODE]

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Advocate ,
Oct 28, 2015 Oct 28, 2015

Copy link to clipboard

Copied

That's a different question, but here is how you do it:

<cfset myarray = arrayNew(1)>

<cfloop query="qryTest">

<cfset recs = structNew()>

<cfset recs.Customer = CustomerID>

<cfset recs.Number = Number>

<cfset arrayAppend(myarray, recs)>

</cfloop>

<cfdump var="#myarray#">

(Sorry about the poor formatting.)

Cheers

Eddie

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Dec 04, 2020 Dec 04, 2020

Copy link to clipboard

Copied

Eddie shows how to do it yourself. Painlessly.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Dec 02, 2020 Dec 02, 2020

Copy link to clipboard

Copied

I created the following until hopefully Adobe ColdFusion might adopt a cool returntype attribute feature that Lucee offers to turn the query type into an array or structs.  Until then or maybe never:

<cfscript>
queryToAOS(yourQueryVariableHere);

function queryToAOS (qryData) {
	local.aos = [];
	for (local.i = 1; local.i <= qryData.recordCount; local.i++) {
		local.aos.append(queryGetRow(qryData, local.i));
	}
	return local.aos;
}
</cfscript>

 

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Dec 03, 2020 Dec 03, 2020

Copy link to clipboard

Copied

Folks (especially to drummingsticks' last comment), some good news is that CF2021 has indeed addressed this, adding the ability to return a query resultset as an array or indeed a json array, whether using cfquery or queryexecute. See the docs for the new ReturnType as an attribute for cfquery or an argument for queryexecute.


If someone might wonder, "how would we ever know that without constantly watching the docs for changes", I will note that it's also listed as one of the new features for CF2021 on this features page: 
https://helpx.adobe.com/coldfusion/user-guide.html/coldfusion/using/whats-new.ug.html 

 

Hope that's helpful.


/Charlie (troubleshooter, carehart.org)

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Dec 04, 2020 Dec 04, 2020

Copy link to clipboard

Copied

LATEST

Here's yet another solution, for those on CF2018 (not CF2016 or earlier), indeed a one-liner, using the reduce and fat arrow functional programming constructs, which Adobe offered starting in CF2018 update 5 (in Sep 2019). 

 

David Byers did a blog post showing the concept and code, and here is a still simpler working example I just created on the cffiddle site

 

[I had originally written that this was also in CF2016 update 12, based on what David had written in his post. But right after posting my comment here, I thought to do a check against CF2016's latest update--as offered on that cffiddle site--and sadly it did NOT work. And my looking at docs elsewhere about these fat arrow/lambda's, they indeed mention only CF2018.)

 

Anyway, to show it here more simply in the context of pirlo89's original post and code above (which granted is from 2015), it would be (using tags as they did):

 

<cfset myarray=qryCustomers.reduce((result, row) => {result.append(row);return result;}, []);

 

 

Sure, that's ugly and even obtuse to those not familar with functional programming (which is supposed to make things "simpler"). But bottom line, it's literally as simple as appending ".reduce((result, row) => {result.append(row);return result;}, []);" to any queryname.

 

Hope that helps folks on CF2018, until they may get to 2021 where it's that simpler returntype="array" that I mentioned yesterday. 🙂


/Charlie (troubleshooter, carehart.org)

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Resources
Documentation