iterating over array data

hi, i am using the following query to get the results in the following format

<cffunction access="public" name="getSettings" returntype="array">
  	<cfset var mySettings = "">
	<cfquery name="mySettings">
      EXEC sp_site_Mail_Templates;
    </cfquery>
	<cfreturn QueryToArray(mySettings)>
</cffunction>

Open in new window


using the querytoarray function to get the results in an array


<cffunction name="QueryToArray" access="public" returntype="array" output="false" hint="This turns a query into an array of structures.">
  <!--- Define arguments. --->
  <cfargument name="Data" type="query" required="yes" />
  <cfscript>    
    var LOCAL = StructNew(); // Define the local scope.
    LOCAL.Columns = data.getMetaData().getColumnLabels(); // Get the column names as an array.
    LOCAL.QueryArray = ArrayNew(1); // Create an array that will hold the query equivalent.
    for (LOCAL.RowIndex = 1 ; LOCAL.RowIndex LTE ARGUMENTS.Data.RecordCount; 
	LOCAL.RowIndex = (LOCAL.RowIndex + 1)){
    	LOCAL.Row = StructNew();
    	for (LOCAL.ColumnIndex = 1 ; LOCAL.ColumnIndex LTE ArrayLen(LOCAL.Columns); 
		LOCAL.ColumnIndex = (LOCAL.ColumnIndex + 1)){
    		LOCAL.ColumnName = LOCAL.Columns[LOCAL.ColumnIndex];
    		LOCAL.Row[LOCAL.ColumnName] = ARGUMENTS.Data[LOCAL.ColumnName][LOCAL.RowIndex];
    	}
    	ArrayAppend(LOCAL.QueryArray, LOCAL.Row);
    }
    return(LOCAL.QueryArray);
    </cfscript>
</cffunction>

Open in new window


now i am trying to use in cfoutput or cloop to fill in my my textareas, i am stuck as to why it is not going

i am trying to use array like this
<cfloop from="1" to="#arrayLen(rsSettings)#" index="i">
<cfoutput>
<form action="#cgi.SCRIPT_NAME#?#cgi.QUERY_STRING#" method="post" id="formSettings" name="formSettings" class="formSettings">
<fieldset><legend>Configure Mail Templates</legend>
  <div class="onleft">
   <label>Account <span>{NAME}, {SUBJECT}, {BODY}, {CREATED}, {LINK}, {PRIORITY}, {DEPARTMENT}</span></label>
    <textarea name="tce" rows="10" class="textareas" cols="50">#data['log']#</textarea>
    <label>Account Updated Email <span>{NAME}, {CREDENTIALS}, {CREATED}</span></label>
    <textarea name="acc" rows="10" class="textareas" cols="50">#data['reg']#</textarea>
    <label>Order Email <span>{NAME}, {CREATED}, {BYWHOM}, {CANNEDMSG}</span></label>
    <textarea name="can" rows="10" class="textareas" cols="50">#data['ord']#</textarea>
  </div>
</form>
</cfoutput>
</cfloop>

Open in new window


but it is not entering the values properly
LVL 16
Gurpreet Singh RandhawaCEOAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

gdemariaCommented:
I don't see how the array / structure in the function matches the structure "data" in the last code post.

I think you should do a cfdump of the value returned from the function and see what you get.    It looks like the format you are creating is : data[RecordNumber][ColumnName]

In your code, you are using data['ord']  with no record/array number

You are looping :  
<cfloop from="1" to="#arrayLen(rsSettings)#" index="i">

rsSettings and then using #data#

you should use  data[ i ]['ord']


Btw, my personal pet peeve is using a single letter for index (such as i ) because it's impossible to search for.   I suggest using  ii   or  jj   or  something like  "aValue"
0
Gurpreet Singh RandhawaCEOAuthor Commented:
here is the screenshot, how it needs to be looking
Capture.PNG
0
gdemariaCommented:
Thank you for the dump image,  my assumption was correct, the first element is the number which you were not using.

Your reference of the structure needs to start with the array position as the image shows... something like

     data[ i ]['ord']
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

Gurpreet Singh RandhawaCEOAuthor Commented:
can you post a complete code
0
gdemariaCommented:
Looking at your image, please tell me which values that you want to use.  For example, I see the value "tce" and "tue" under the "name" element.   Are those the values you want to use?
0
Gurpreet Singh RandhawaCEOAuthor Commented:
yes, if i call tce, it should call the html_body in the textarea and i need to save them too in the database after a change
0
gdemariaCommented:
I hope this will help, I don't know what your code is about, but this is how to loop through that structure.
I don't know why your CFLOOP used RSSETTING, so I am using DATA as the variable.  I assume the image is a dump of #data#...

<cfoutput>
<cfloop from="1" to="#arrayLen(data)#" index="kk">
    <cfset theStruct = data[kk]>
    <cfif Struct['Name'] is "tce">
	  Found tce: #struct['html_body']#<br>
	<cfelse>
	  Other: #struct['name']# is #struct['html_body']#<br>
	</cfif>
</cfloop>   
</cfoutput>

Open in new window

0
Gurpreet Singh RandhawaCEOAuthor Commented:
its making multiple textareas when i am looping over, why it is happening

<cfloop from="1" to="#arrayLen(data)#" index="i">
<cfset theStruct = data[i]>
  <div class="onleft">
   <label>Account Registration<span></span></label>
    <textarea name="tce" rows="10" class="textareas" cols="50"><cfif theStruct['Name'] is "tce">#theStruct['html_body']#</cfif></textarea>
    <label>Account Updated Email <span></span></label>
     <textarea name="acc" rows="10" class="textareas" cols="50"><cfif theStruct['Name'] is "tue">#theStruct['html_body']#</cfif></textarea>
    <label>Order Email <span></span></label>
    <textarea name="can" rows="10" class="textareas" cols="50"><cfif theStruct['Name'] is "nar">#theStruct['html_body']#</cfif></textarea>
  </div>
  <div class="onarse">
    <label>New Account Registration <span>{NAME}, {CREDENTIALS}, {DATE}</span></label>
    <textarea name="nar" class="textareas" cols="50"><cfif theStruct['Name'] is "fpe">#theStruct['html_body']#</cfif></textarea>
    <label>Forgot Password Activation <span></span></label>
    <textarea name="fpe" class="textareas" cols="50"><cfif theStruct['Name'] is "pae">#theStruct['html_body']#</cfif></textarea>
    <label>Password Reset <span>{NAME}, {CREDENTIALS}, {CREATED}</span></label>
    <textarea name="pae" class="textareas" cols="50"><cfif theStruct['Name'] is "acc">#theStruct['html_body']#</cfif></textarea>
    <label>Updated Email <span></span></label>
    <textarea name="tue" class="textareas" cols="50"><cfif theStruct['Name'] is "can">#theStruct['html_body']#</cfif></textarea>
  </div>
  <br class="clr" />
  <label></label><br class="clr"/>
</cfloop>

Open in new window

0
gdemariaCommented:
You don't have anything to NOT create the text areas.   You have about 7 textarea tags for every iteration of the loop.   If you don't want all seven created each loop, then you have to have some code to not create that box.    What do you expect the final result to be? Maybe you don't want to loop at all?
0
Gurpreet Singh RandhawaCEOAuthor Commented:
no i don't want that

let's do this way, here is the result of the query what i am getting, leave all arrays and structures behind, now i want that for every record, it should have its entry in its own textarea, please find the attached image
Capture.PNG
Capture2.PNG
0
gdemariaCommented:
Ok, just to be sure I understand..
The image shows  9 records, but each record has a different value in the "name" column.   What you want is ONE textarea field for every value in the name column.

<textarea name="tce"> Hello.....</textarea>

<textarea name="tue">Hello.... </textarea>

Is that right?  So for this example, you want 9 text area fields the content is HTML_BODY and the name of the text area is the value in the NAME field (tce, tue, var, etc...)

Did I get it right?
0
gdemariaCommented:
Assuming what I said is true and that you have only one occurrence of each textarea field, then your code can be much simpler, you don't need the function.   You also don't need an array because there is going to be only one "record" or occurrence of each field name.  

This example creates a structure, makes an empty placeholder for every possible value for the textarea name, then populates the structure from the query using the name and html_body columns.   Then, without looping (no loop needed!)  you show the textarea fields with the value from the structure


<cfset theStruct = structNew()> <!--- create an empty structure ---->
<!---- create an empty key for every possibility to keep from having to test on every line below ---->
<cfset theStruct = {
    tce = "",
    tue = "",
    nar = "",
    fpe = "",
    pae = "",
    acc = "",
    can = ""
    } />

<cfloop query="myQuery"> <!---- overwrite the empty values with the data from the query ---->
   <cfset theStruct[myQuery.name] = data.HTML_body>
</cfloop>

  <div class="onleft">
   <label>Account Registration<span></span></label>
    <textarea name="tce" rows="10" class="textareas" cols="50">#theStruct['tce']#</textarea>
    <label>Account Updated Email <span></span></label>
    <textarea name="acc" rows="10" class="textareas" cols="50">#theStruct['acc']#</textarea>
    <label>Order Email <span></span></label>
    <textarea name="can" rows="10" class="textareas" cols="50">#theStruct['can']#</textarea>
  </div>
  <div class="onarse">
    <label>New Account Registration <span>{NAME}, {CREDENTIALS}, {DATE}</span></label>
    <textarea name="nar" class="textareas" cols="50">#theStruct['nar']#</textarea>
    <label>Forgot Password Activation <span></span></label>
    <textarea name="fpe" class="textareas" cols="50">#theStruct['fpe']#</textarea>
    <label>Password Reset <span>{NAME}, {CREDENTIALS}, {CREATED}</span></label>
    <textarea name="pae" class="textareas" cols="50">#theStruct['pae']#</textarea>
    <label>Updated Email <span></span></label>
    <textarea name="tue" class="textareas" cols="50">#theStruct['tue']#</textarea>
  </div>
  <br class="clr" />
  <label></label><br class="clr"/>

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Gurpreet Singh RandhawaCEOAuthor Commented:
Thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ColdFusion Language

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.