ColdFusion looping over query with cfqueryparam

Question

I am trying to loop over query with a nested query. The code that I have so far:

<cfloop query="hashTableLatest">
    <cfquery name="getDifferentImages" dbtype="query">
        SELECT image, imageHash
        FROM hashTable
        WHERE imageHash = <cfqueryparam cfsqltype="cf_sql_varchar" value="#hashTableLatest.imageHash#" />
    </cfquery>
</cfloop>

The problem that I have is that it doesn't loop dynamically through the cfqueryparam. It just gets the first value from the hashTableLatest. Can anyone tell me what am I doing wrong? How can I loop through a query and dynamically change the cfqueryparam?

EDITED To get all the information I need in a single query:

select a.imageHash
from tblHashLatest a
WHERE a.imageHash in (SELECT c.imageHash
                  FROM tblHash c
              WHERE a.imageHash <> c.imageHash)

I think that the above SQL should get me all the information I need. The result I am looking for is to get all imageHashes that are not same

Your code is correct and it should be doing what you want. What is making you think that it isn't? Have you used CFDUMP on the inner query to check a) what it contains; b) the SQL & params being used? — Adam Cameron, Jan 17 '14 at 13:28
Why are you looping over a query and executing another query in that loop? Look into JOINs and you will be able to do this all in one query, — Scott Stroz, Jan 17 '14 at 13:58

score 3 · Accepted Answer · answered Jan 17 '14 at 13:28

3

There are a couple of options. One is to not use a loop and just do this:

WHERE imageHash in ( 
<cfqueryparam cfsqltype="cf_sql_varchar" 
value="#ValueList(hashTableLatest.imageHash)#" list="yes">
)
</cfquery

If possible, you should look for ways to get all the information you need from a single query.

answered Jan 17 '14 at 13:28

Dan Bracuk

20,699
4
26
43

4

Agreed. Performing additional CFQUERY calls within a loop of a first CFQUERY result is almost always a suboptimal approach to a requirement. – Adam Cameron Jan 17 '14 at 13:29
2

Using a `JOIN` would be preferable and it should be able to be done in a single query. – Scott Stroz Jan 17 '14 at 13:59

ColdFusion looping over query with cfqueryparam

1 Answers1