Solved

joining form submission with query output

Posted on 2003-11-24
9
242 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
 
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
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

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…
Periodically we have to update or add SSL certificates for customers. Depending upon your hosting plan you may be responsible for the installation and/or key generation. In the wake of Heartbleed many sites were forced to re-key. We will concen…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

707 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now