Array in Where clause?

Forum|Forum|14 years ago
September 22, 2011
4 replies
6300 views

I would like to create a Coldfusion function where one of my arguments is and array of "user_id"'s. Then I want to:

Select * from database where (each id in the array) = #user_id#

How is this written?

Database access

This topic has been closed for replies.

BKBK

Community Expert

@C-Rock

Should you be interested, the clause where the fuss is all about is:

WHERE id IN (<CFQUERYPARAM VALUE="#IDList#" CFSQLTYPE="CF_SQL_INTEGER" LIST="yes">)

I have assumed the user IDs are whole numbers.

BKBK

Community Expert

C-Rock wrote:
I would like to create a Coldfusion function where one of my arguments is and array of "user_id"'s. Then I want to:
Select * from database where (each id in the array) = #user_id#
How is this written?

SELECT *

FROM myTable

WHERE id IN (#IDList#)

</cfquery>

</cffunction>

-

-__cfSearching__-

Inspiring

WHERE id IN (#IDList#)

Do not forget to use cfqueryparam to avoid sql injection. Also remember to scope query variables too.

BKBK

Community Expert

-==cfSearching==- wrote:
WHERE id IN (#IDList#)
Do not forget to use cfqueryparam to avoid sql injection. Also remember to scope query variables too.

I would disagree. If there is a need to use cfqueryparam here, then the design is likely improper.

User IDs don't usually come from users themselves. One may therefore assume that, by the time we arrive at an in-process like this one, the IDs have been validated and are kosher (or halal, as the case may be).