change
CONTAINS '#category#'
to
LIKE '%#category#%'
Better yet, normalize the db... each field should be atomic....
this form of search is very slow as each record must be examined...
if the field were atomic, the search could take place entirely in the
indexes.
the point here, is to let the *relational* aspect of the db work for you!
hth
dick
At 4:24 PM -0700 4/24/01, Metzy Martinez wrote:
>Hello-
>
>I have a problem. I have a bridge table and in that table i have
>category
> and
>category_other columns
>
>category_other is a comma delimited list of all other types of categories it
>can
>
>my question is how can i search this list if i'm searching for one specific
>category for example clubs and i want to know if it is also located in the
>category_others
>
>**can i use the word contains
>
>
>
>
><cfquery name="lists" datasource="#dsn.LocalGuide#" dbtype="ODBC">
> Select x.*
> From #table# x, local_bridge l
> Where (((l.Category_Others CONTAINS '#category#') or (l.category =
>#category#)) and (x.id = l.co_id))
>
> <cfif name is not "">AND
> ((x.Name LIKE '%#name#%'))</cfif>
> <cfif category is not "">AND
> ((x.CategoryMain = '#category#') OR
> (x.CategoryOther = '#category#'))</cfif>
> <cfif street is not ""> AND
> (x.Street1 LIKE '%#street#%')</cfif>
> <cfif keyword is not "">AND
> ((x.Keywords LIKE '%#keyword#%') OR
> (x.Name LIKE '%#keyword#%') OR
> (x.Description LIKE '%#keyword#%'))</cfif>
> <cfif area is not "">AND (x.AreaOfTown = '#area#') </cfif>
> <cfif yourReview is "on">AND (x.NumberOfVotes > 0)</cfif>
> <cfif criticReview is "on">AND (x.reviewid <> '') </cfif>
> <cfif award is "on">AND (x.AwardsID <> '')</cfif>
> ORDER BY #OrderBy#
> </cfquery>
>
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
