Solved

using loop around a query

Posted on 2004-10-25
155 Views
Last Modified: 2013-12-24
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
Question by:sdowns1122
    3 Comments
     
    LVL 35

    Accepted Solution

    by:
    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
     
    LVL 5

    Assisted Solution

    by:kkhipple
    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
     

    Author Comment

    by:sdowns1122
    thanks for the help guys. i ended up placing the results into an array.
    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Anonabox PRO Tor & VPN Router

    PRO is the most advanced way to fortify your privacy and online anonymity by layering the Tor network with VPN services. Use both together or separately, and without needing to download software onto your devices.

    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…
    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 Experts Exchange video Micro Tutorial, I'm going to show how small business owners who use Google Apps can save money by setting up what is called a catch-all email address in their Gmail accounts. By using the catch-all feature, small busin…
    Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…

    933 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

    17 Experts available now in Live!

    Get 1:1 Help Now