• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 176
  • Last Modified:

using loop around a query

hello,

first off, here are my 2 queries:

<!--- query for veiws and other ID info --->
<cfquery name="GetMostViews" datasource="DB" maxrows="5">
select Top 5 Tables.Maintable, Tables.ID, LinkInfoID, Views
from Clicks_#cookie.regulator#
left join Tables on Clicks_#cookie.regulator#.TableNameID = Tables.ID
</cfquery>

<!--- query for veiws and other ID info --->
<cfloop query="GetMostViews">
<cfquery name="GetLinkInfo" datasource="DB">
select LinkURL, LinkTitle
from #GetMostViews.MainTable#
where ID = #GetMostViews.LinkInfoID#
</cfquery>
</cfloop>

what i need these two queries to accomplish is for the 2nd query to use MainTable and LinkInfoID from the 1st query to retrieve info. in theory it should loop and cycle thru the 5 results from the 1st query, but it's not doing so. any thoughts?

thanks
steve
0
sdowns1122
Asked:
sdowns1122
2 Solutions
 
mrichmonCommented:
Well the second query is gettin grun 5 times and each time the output is overwriting the previous time.

Maybe you want somehting like:

<cfquery name="GetLinkInfo" datasource="DB">
<cfif GetMostViews.currentRow NEQ GetMostViews.RecordCount>
select LinkURL, LinkTitle
from #GetMostViews.MainTable#
where ID = #GetMostViews.LinkInfoID#
UNION
<cfelse>
select LinkURL, LinkTitle
from #GetMostViews.MainTable#
where ID = #GetMostViews.LinkInfoID#
</cfif>
</cfquery>
0
 
kkhippleCommented:
Have you tried outputting the information you have within the loop?
You dont need to reference #GetMostViews# again within your query

<!--- query for veiws and other ID info --->
<cfquery name="GetMostViews" datasource="DB" maxrows="5">
select Top 5 Tables.Maintable, Tables.ID, LinkInfoID, Views
from Clicks_#cookie.regulator#
left join Tables on Clicks_#cookie.regulator#.TableNameID = Tables.ID
</cfquery>

<!--- query for veiws and other ID info --->
<CFOUTPUT query="GetMostViews">
<cfquery name="GetLinkInfo" datasource="DB">
select LinkURL, LinkTitle
from #MainTable#
where ID = #LinkInfoID#
</cfquery>

#LinkURL# #LinkTitle#
</CFOUTPUT>
0
 
sdowns1122Author Commented:
thanks for the help guys. i ended up placing the results into an array.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now