Solved

joining form submission with query output

Posted on 2003-11-24
9
247 Views
Last Modified: 2013-12-24
I have a form which submits raw data whic is to be compiled and commented on in the submission page, the compiled data is stored in a mutlidimentional array with the ket to a database item stored at [x][1]
the array is sorted in descending orderby the total [x][5] sample;
B-1-1-3-5
A-2-1-1-4
C-1-2-1-4
D-1-1-1-3
E-1-1-1-3
F-1-1-1-3
G-1-1-1-3
H-1-1-1-3
I-1-1-1-3
J-1-1-1-3

how can i loop the array and retreive the corresponding database reference without having to query the databse at each iteration of the loop?
B-1-1-3-5
^ key to database item
B field1 field2
databse table
0
Comment
Question by:James Rodgers
  • 4
  • 3
  • 2
9 Comments
 
LVL 11

Expert Comment

by:hart
ID: 9815840
http://livedocs.macromedia.com/coldfusion/6/Developing_ColdFusion_MX_Applications_with_CFML/arrayStruct4.htm

check this link out for looping on multidimensional arrays.

Regards
Hart
0
 
LVL 10

Expert Comment

by:Mause
ID: 9816240
I'm not sure what you exactly want but the link from Hart will explane you how to loop over a 2d array.

it will be something like this I gues:

<cfloop index="loopy" from="1" to="#ArrayLen(myarray)#">
   #loopY#
  <cfloop index="loopx" from="1" to="#ArrayLen(myarray[loopy])#">
        -#loopx#
  </cfloop>
   <br>
</cfloop>

The output of this loop will be
B-1-1-3-5
A-2-1-1-4
C-1-........

Mause
0
 
LVL 25

Author Comment

by:James Rodgers
ID: 9817379
I probably wasn't clear enough in the description
i have an array which i have populated with form submission details and sorted according to a specific value

i want to use the array and the results of a query to output combined details where the item data in array[x][1]=the key field value form the array - without having to requery on each iteration so rather than

<cfloop from="1" to="#arrayLen(myArray)#" index="idx">
   <cfquery name="getElements">
      SELECT QID, QTEXT, SKILLREF
      FROM ELEMENTS
      WHERE QID=#myArray[idx][1]#
   </cfquery>
   <cfloop query="getElements>
      //output details here
   </cfloop>
</cfloop>

i was looking for a method to accomplish the same results without needing to have a query within the loop

more like

<cfloop from="1" to="#arrayLen(myArray)#" index="idx">
   //find the row from query getElements where row[1]=#myArray[idx][1]#
   // outoput the details
</cfloop>
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 11

Accepted Solution

by:
hart earned 500 total points
ID: 9817439
ok is this what u want a single query to show details


 <cfquery name="getElements">
     SELECT QID, QTEXT, SKILLREF
     FROM ELEMENTS
 </cfquery>

<cfset Lst_QID = ValueList(getElements.QID,'~')>
<cfset Lst_QTEXT = ValueList(getElements.QTEXT,'~')>
<cfset Lst_SKILLREF = ValueList(getElements.SKILLREF,'~')>


<cfloop from="1" to="#arrayLen(myArray)#" index="idx">
   <cfset nFind = ListFind(Lst_QID,myArray[idx][1],'~') >
   <cfif nFind NEQ 0>
       <cfoutput>
       QID : #myArray[idx][1]#
       QTEXT : #ListGetAt(Lst_QTEXT,nFind,'~')#
       SKILLREF : #ListGetAt(Lst_SKILLREF,nFind,'~')#
       </cfoutput>
   </cfif>
</cfloop>

Regards
Hart
0
 
LVL 25

Author Comment

by:James Rodgers
ID: 9817499
perfect!!

0
 
LVL 25

Author Comment

by:James Rodgers
ID: 9817508
thanks hart,

the find and list functions are two things i don't use enough to get a good understanding of thei capabilities

increased the points
0
 
LVL 10

Expert Comment

by:Mause
ID: 9817514
Thats it hart!

But you only need one valuelist you can get the others by using this:
QTEXT : #getElements.QTEXT[nFind]#
SKILLREF : #getElements.SKILLREF[nFind]#



Mause
0
 
LVL 11

Expert Comment

by:hart
ID: 9817574
thanks man :-)

And Yeah Good One Mause !

Regards
Hart
0
 
LVL 25

Author Comment

by:James Rodgers
ID: 9817794
Mause

I incorparated your code into the routine, please go here http://www.experts-exchange.com/Web/WebDevSoftware/ColdFusion/Q_20808116.html to get your points
0

Featured Post

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Problem to go to page 12 110
IIS 404 Error on website Automatic Redirect to /help 5 68
ip / url redirect 13 74
How to fix Redundant Hostnames notification from google analytic? 5 55
In our day to day coding, how many times have we come across a necessity to check whether a URL is a broken link or not? For those of you that answered countless and are using ColdFusion like myself, then this article is for you.  It will show yo…
Have you ever sent email via ColdFusion and thought of tracking this mail to capture the exact date and time when the message was opened ?  If yes, then this article is for you ! First we need a table user_email with columns user_id , email , sub…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

735 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question