Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations bkrike on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
converting query to list
- Thread starter MarcDePoe
- Start date
Hey Marc,
You could just do a <cfloop> like this to build an array:
<cfloop query="q1">
<cfset ar1[currentRow] = q1.field1>
</cfloop>
If you want a list instead, this should work:
<cfloop query="q1">
<cfset list1 = listappend(list1,q1.field1)>
</cfloop>
Is this what you're looking for?
GJ
You could just do a <cfloop> like this to build an array:
<cfloop query="q1">
<cfset ar1[currentRow] = q1.field1>
</cfloop>
If you want a list instead, this should work:
<cfloop query="q1">
<cfset list1 = listappend(list1,q1.field1)>
</cfloop>
Is this what you're looking for?
GJ
- Thread starter
- #3
Actually, the query results can get pretty big, and I thought that maybe since you can get at query records like this...
queryname[4]
....
that there would be some way to do something like this...
<cfset ListVariable=ArrayToList(one-field-yielding-query)>
but all I got was errors, and looping would defeat the purpose in my case...
right now I'm doing the second query like this...
DELETE from TABLENAME
where 0=1
<cfloop query="QUERYNAME">
OR field='#field#'
</cfloop>
which works fine ...
but I know that MySql,which is what I'm using, can process IN operations much faster..hence my want (not neccesarily need) for a list.
but thank you
queryname[4]
....
that there would be some way to do something like this...
<cfset ListVariable=ArrayToList(one-field-yielding-query)>
but all I got was errors, and looping would defeat the purpose in my case...
right now I'm doing the second query like this...
DELETE from TABLENAME
where 0=1
<cfloop query="QUERYNAME">
OR field='#field#'
</cfloop>
which works fine ...
but I know that MySql,which is what I'm using, can process IN operations much faster..hence my want (not neccesarily need) for a list.
but thank you
- Thread starter
- #4
Hey Marc,
Your idea is clever but I suspect the reason it's not working is that CF doesn't treat a query result internally as an array. The syntax is the same but I'd bet the internal data structures are quite different.
Is there any reason you wouldnt' want to do something like this:
delete from TABLENAME where field IN (select fieldName from Table2 where ..)
I've never tried a delete this way but I think this is what you're trying to accomplish.
GJ
Your idea is clever but I suspect the reason it's not working is that CF doesn't treat a query result internally as an array. The syntax is the same but I'd bet the internal data structures are quite different.
Is there any reason you wouldnt' want to do something like this:
delete from TABLENAME where field IN (select fieldName from Table2 where ..)
I've never tried a delete this way but I think this is what you're trying to accomplish.
GJ
- Thread starter
- #6
MarcDePoe, the easiest way around this is hang on to a list. Following code will work:
<cfquery name="MyFirstQuery" datasource="#DSN#">
SELECT ID_field
FROM Table1
WHERE conditions...
</cfquery>
<cfset RemoveList=ValueList(MyFirstQuery.ID_field)>
<!--- The variable RemoveList will hold all the ID_fields
of the records to be removed --->
<cfquery name="MySecondQuery" datasource="#DSN#">
DELETE from TABLENAME
WHERE ID_field IN (#RemoveList#)
</cfquery>
Hope this helps...
<webguru>iqof188</webguru>
<cfquery name="MyFirstQuery" datasource="#DSN#">
SELECT ID_field
FROM Table1
WHERE conditions...
</cfquery>
<cfset RemoveList=ValueList(MyFirstQuery.ID_field)>
<!--- The variable RemoveList will hold all the ID_fields
of the records to be removed --->
<cfquery name="MySecondQuery" datasource="#DSN#">
DELETE from TABLENAME
WHERE ID_field IN (#RemoveList#)
</cfquery>
Hope this helps...
<webguru>iqof188</webguru>
- Status
Similar threads
