Ask a Question related to Coldfusion Database Access, Design and Development.
-
TimMcGeary #1
problem looping through queries
I am attempting to create a loop that will give me all records in a table whose
ids are set to a certain value in a related table. The logic I am using is
that I loop through the "status" table to find which request_id=1.
With those request_ids, I am querying the requests table to output the
pertinent information, which includes a requestor_id that is an id for the
requestor table. I then query the requestor table to output from there.
My problem is that though I am showing the unique request_ids that are set to
that value, the output of table request and table requestors is only that of
the last request_id value in the loop. Why? What am I doing wrong?
The code:
<cfset reqIDArray=arraynew(1)>
<cfquery name="get_open_requests" datasource="storage">
SELECT request_id FROM status WHERE status_id=1 ORDER BY request_id
</cfquery>
<cfloop query="get_open_requests">
<cfset reqIDArray[CurrentRow] = #request_id#>
</cfloop>
<cfset numRequests=get_open_requests.recordcount>
<cfset reqArray=arraynew(2)>
<cfloop index="index" from=1 to="#numRequests#">
<cfquery name="open_request_info" datasource="storage" >
SELECT request_time, title, author, callnum, requestor_id FROM requests
WHERE request_id=#reqIDArray[index]#
</cfquery>
<cfquery name="get_requestor_info" datasource="storage">
SELECT firstname, lastname, LIN, email FROM requestors WHERE
requestor_id=#open_request_info.requestor_id#
</cfquery>
</cfloop>
<!-- <body stuff and table headers> -->
<cfloop index="index" from=1 to="#numRequests#">
<tr><form action="view_request" method="get">
<td><cfoutput>#reqIDArray[index]#</cfoutput></td>
<td><cfoutput query="open_request_info">#request_time#</cfoutput></td>
<td><cfoutput
query="get_requestor_info">#firstname# #lastn ame#</cfoutput></td>
<td><cfoutput query="get_requestor_info">#LIN#</cfoutput></td>
<td><cfoutput query="get_requestor_info">#email#</cfoutput></td>
<td><cfoutput query="open_request_info">#title#</cfoutput></td>
<td><cfoutput query="open_request_info">#author#</cfoutput></td>
<td><cfoutput query="open_request_info">#callnum#</cfoutput></td>
<td><input name="request_id" type="hidden"
value="<cfoutput>#reqIDArray[index]#</cfoutput>">
<input name="view" type="submit" value="view request"></td></form>
</tr>
</cfloop>
TimMcGeary Guest
-
Problem Looping
I am trying to create an array of structures using data collected from a form. The form contains 5 sets of seven fields each set containing... -
Possible bug: looping over queries within loops
Don't know if this has been discussed already, but I am having problems with CF. I have one cfquery that I run that returns a recordset. I am... -
Problem with looping over cfhttp
Hi, I am trying to get some image files from a remote server and save them to my server. I have all the paths in a query but when I loop over... -
looping over a list problem
I am having a problem looping over a list with some elements that are empty. Here is a sample of the list: address_street=8349+White+Dr custom=... -
Queries Of Queries Single Quote Problem
When using queries of queries I'm having the following issue. Select Company_ID From qry_MyQuery Where Company_NM = 'MyString''s' <----... -
mxstu #2 Re: problem looping through queries
I could be wrong, but I think you could greatly simplify this code and possibly
retrieve the information in a single query. What is the reason you're using an
array and all the nested loops and CFQUERIES? Is it just to output sets of
related information in separate tables?
mxstu Guest
-
TimMcGeary #3 Re: problem looping through queries
The only reason is that it's an example I found in the help when I was struggling with not getting the output I expected. Could you give me an example of a single query that you are thinking of?
TimMcGeary Guest
-
mxstu #4 Re: problem looping through queries
You INNER JOIN the three tables together on the shared columns
status.request_id ---> requests.request_id
requests.requestor_id ---> requestors.requestor_id
--- NOT tested . Should be suitable for Access
SELECT r.request_id, r.request_time, r.title, r.author, r.callnum,
rby.firstname, rby.lastname, rby.LIN, rby.email
FROM (status s INNER JOIN requests r WHERE s.request_id = r.request_id)
INNER JOIN requestors rby ON r.requestor_id = rby.requestor_id
WHERE s.status_id = 1
If my query contains no typos ;-) this should give you the same results as
contained your array. You should be able to just use a single CFOUTPUT
QUERY="..." loop to display the results.
mxstu Guest
-
TimMcGeary #5 Re: problem looping through queries
Sorry this took so long to reply, but when I try that, I am getting an error:
Element REQUEST_ID is undefined in R.
<cfquery name="get_open_requests" datasource="storage">
SELECT r.request_id, r.request_time, r.title, r.author, r.callnum,
rby.firstname, rby.lastname, rby.LIN, rby.email
FROM (status s INNER JOIN requests r ON s.request_id = r.request_id)
INNER JOIN requestors rby ON r.requestor_id = rby.requestor_id
WHERE s.status_id = 1
</cfquery>
[snip header, style, and some body and table stuff]
<cfoutput query="get_open_requests">
<tr><form action="view_request" method="get">
<td>#r.request_id#</td>
<td>#r.request_time#</td>
<td>#rby.firstname# #rby.lastname#</td>
<td>#rby.LIN#</td>
<td>#rby.email#</td>
<td>#r.title#</td>
<td>#r.author#</td>
<td>#r.callnum#</td>
<td><input name="request_id" type="hidden" value="r.request_id">
<input name="requestor_id" type="hidden" value="rby.requestor_id">
<input name="view" type="submit" value="view request"></td>
</form>
</tr>
</cfoutput>
TimMcGeary Guest
-
mxstu #6 Re: problem looping through queries
I think the usage of "aliases" in the query statement is confusing you. r is
an "alias" used ONLY within the sql query. It's basically a shorthand way of
referencing table names in a JOIN statement. So ....
... SELECT r.request_id ... INNER JOIN requests r
is just a shorter way of writing this...
... SELECT requests.request_id ... INNER JOIN requests ....
The "aliases" in the example are used only by the database engine and do not
exist as far as CF is concerned. This is why you received the error "REQUEST_ID
is undefined in R". Just remove all of "r." references in your CFOUTPUT code.
<td>#r.request_id#</td>
should be
<td>#request_id#</td>
mxstu Guest
-
TimMcGeary #7 Re: problem looping through queries
Thanks, Mxstu! I did misunderstand. I assumed the alias needed to be part of the query output, as well. This helps me a great deal. Thank you, also, for being patient with my learning.
TimMcGeary Guest
-
mxstu #8 Re: problem looping through queries
You're very welcome. I learn from the posts on these forums as well! IMO
understanding why something is a solution is more important than the solution
itself. If you don't understand it, you cannot apply the knowledge in future
situations ;-)
mxstu Guest
-
ploring #9 Re: problem looping through queries
Using the one query with the joins was definately the right solution, but I
thought, since you are learning CF, that it might be helpful to know why your
first code wasnt working.
Let me run through the main sections of your code and explain what you were
doing. It should become clear from that what you were doing wrong.
This portion gets all your open requests and puts them into an array.
----------------------------------------
<cfset reqIDArray=arraynew(1)>
<cfquery name="get_open_requests" datasource="storage">
SELECT request_id FROM status WHERE status_id=1 ORDER BY request_id
</cfquery>
<cfloop query="get_open_requests">
<cfset reqIDArray[CurrentRow] = #request_id#>
</cfloop>
---------------------------------
Next, you're looping over the array, and running two queries: one to get
request info and one to get requestor info
---------------------------------
<cfloop index="index" from=1 to="#numRequests#">
<cfquery name="open_request_info" datasource="storage" >
SELECT request_time, title, author, callnum, requestor_id FROM requests
WHERE request_id=#reqIDArray[index]#
</cfquery>
<cfquery name="get_requestor_info" datasource="storage">
SELECT firstname, lastname, LIN, email FROM requestors WHERE
requestor_id=#open_request_info.requestor_id#
</cfquery>
</cfloop>
--------------------------------
Notice here, that you're looping through all the results in the array, but not
doing anything with it. Each iteration of the loop overwrites the
open_request_info and get_requestor_info queries! That's why when, in your
third section, when you loop over the array again, the values from the other
two queries are just from the last request.
If you had moved the last portion to within the second loop, just after you
ran the two queries, this would have worked for you (albeit very inefficient).
Like i said to start, the best solution was to use the joins - joins are your
friends. you will get better and better and doing things with less loops via
more elegant querying
ploring Guest
-
Reply With QuoteSimilar Questions and Discussions
Posting Permissions
- You may not post new threads
- You may post replies
- You may not post attachments
- You may not edit your posts
