Solved

joining form submission with query output

Posted on 2003-11-24
9
245 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

One of the typical problems I have experienced is when you have to move a web server from one hosting site to another. You normally prepare all on the new host, transfer the site, change DNS and cross your fingers hoping all will be ok on new server…
When it comes to showing a 404 error page to your visitors, you do not want that generic page to show, and you especially do not want your hosting provider’s ad error page to show either. In this article, I will show you how to enable the custom 40…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

773 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