Array? List? Collection? Loop?

[wam4]

I have a form that uses cfselect where the user can select multiple items. I am sending the category id to the next page. When I do a dump of the form for #form.category# I get the correct items in a "list" as in " 184, 450, 38, 233". On the next page I need to select the category names for all the id's the user selects.(They can select 1-400) This is throwing me off on how to set up the item in order to even start the query.

I tried stripping out individual items using this
<cfset firstcat = Right(form.category, Len(trim(form.category))- Find(",",form.category,1))>

That worked for the first one but isn't their a better way to do this dynamically since I'm not going to know how many categories they select. My deliminater is the comma

Then how do I set the query up in order to select one or more categories?
Select * from cat_area where area_id = '#trim(firstcat)#' - this obviously only works for the 1st category.

I've been trying arrays and lists but I think I can't see the forest for the trees anymore. Any help would be greatly appreciated. Thanks

[-__cfSearching__-]

wam4 wrote:
> I need to select the category names for all the id's the user selects.

It is much simpler than you are thinking. You can use the sql keyword IN to search for multiple id values.

SELECT Columns
FROM cat_area
WHERE area_id IN
(
<cfqueryparam value="#trim(form.category)#" list="true" cfsqltype="(whatever your data type is)">
)

> I get the correct items in a "list" as in " 184, 450, 38, 233"

Watch out for spaces in between list elements. It can throw off searches on varchar fields. Though your fields look numeric so that may not apply here.

[wam4]

It didn't like the work "IN" so I tried "where area_id like" which seems like it's moving in the write direction only the problem is with the commas. I'm getting this error message:
Error Executing Database Query.
[Macromedia][Sybase JDBC Driver][Sybase]Incorrect syntax near ','.

The error message for "In" is incorrect syntax near "where"

[-__cfSearching__-]

wam4 wrote:
> It didn't like the work "IN" so I tried "where area_id like"
> which seems like it's moving in the write direction

No, you cannot use LIKE. It will only compare a value against a single pattern.

Sybase supports the keyword IN so I suspect there is a syntax error. Can you post the exact query code?

[wam4]

<cfquery datasource="swp" name="getcategory">
select * from cat_area

WHERE area_id in #form.category#
(
<cfqueryparam value="#trim(form.category)#" list="true" cfsqltype="Char">
)
</cfquery>

The error is Error Executing Database Query.
[Macromedia][Sybase JDBC Driver][Sybase]Incorrect syntax near '467'.

and the number 467 is the first area_id in the list.

In the error message it shows the SQL as select * from cat_area WHERE area_id in 467 ,390 ,29 ,92 ,349 ( (param 1) , (param 2) , (param 3) , (param 4) , (param 5) )

so it appears to be bombing on the comma.

[Newsgroup_User]

wam4 wrote:
> WHERE area_id in #form.category#
> (
> <cfqueryparam value="#trim(form.category)#" list="true" cfsqltype="Char">
> )


You don't see something a but redundant and confusing in that code?

Try it with just the *one* form.category inside the <cfqueryparam...> tag.

[wam4]

That did it - thank you - that was a homer simpson moment - doh!

[-__cfSearching__-]

wam4 wrote:
> <cfqueryparam value="#trim(form.category)#" list="true" cfsqltype="Char">

Also "char" is not a valid cfsqltype type. It should be something like cf_sql_char, cf_sql_varchar, etcetera. The type corresponds to the column data type.
http://livedocs.adobe.com/coldfusion/7/htmldocs/00000317.htm