Looping question

[pixiesfb]

I have a multiple select list called categories, with numeric values:

<select name=&quot;searchcats&quot; multiple>
<option value=1>Item A
<option value=2>Item B
<option value=3>Item C...

I submit the form, searching the &quot;categories&quot; field, which is a comma delimeted list of numbers, (1,3,19,44) for example.

The following loop works well, but it's not what I want to do. If user selects option 1 and 2, only records with BOTH 1 and 2 in the comma delimited categories field will be found. I want all records with EITHER 1 or 2 in the categories field.

I'm probably being bone-headed here. Anyway, appreciate any help.

<cfloop list=#searchcats# index=&quot;x&quot;>
and ( categories like '#x#'
or categories like '%,#x#'
or categories like '#x#,%'
or categories like '%,#x#,%'
)
</cfloop>

 

[pixiesfb]

Answered my own question!

<cfif form.categories is not &quot;&quot;>
<cfset loopvariable=0>
<cfloop index=&quot;categoryIndex&quot; list=&quot;#form.categories#&quot;>
<cfif loopvariable is 0>
and (
<cfelse>
or
</cfif>
categories like '#categoryIndex#'
or categories like '%,#categoryIndex#'
or categories like '#categoryIndex#,%'
or categories like '%,#categoryIndex#,%'
<cfset loopvariable=1>
</cfloop>
)

</cfif>

 

[Guest_imported]

Here is a little &quot;trick&quot; that eliminates the

<cfif loopvariable is 0>
and (
<cfelse>
or
</cfif>

and thus speeds up your code slightly. You won't have to check to see if this is the first item in the list and you can eliminate the loopvariable:

and (1 = 1
<cfloop index=&quot;categoryIndex&quot; list=&quot;#form.categories#&quot;>
or categories like '#categoryIndex#'
or categories like '%,#categoryIndex#'
or categories like '#categoryIndex#,%'
or categories like '%,#categoryIndex#,%'
</cfloop>
)

The AND phrase is always true, so your query will be correct whether or not you have any OR phrases.

 

[pixiesfb]

Thanks!!