Non working search (keywords)

[Newsgroup_User]

I am attempting to make a simple search engine on a web site. it has 3
parameters. Service, State, and Keywords. I pass the 3 criteria's to a
results page that invokes a component. If you just choose the service and
state it works fine, but if you enter a keyword it fails.

In my database I have a dummy company set to a service and a state and uses:
"Quote, design, print, web, posters, signs" in a keyword column.

When I enter the correct service and state it comes up just fine, but if I
enter the word "web" into the keywords it doesn't find the same listing.

Here is my component:
<cfcomponent>
<cffunction name="ServiceSearch" access="remote" returntype="query">
<cfargument name="service" type="string" required="true">
<cfargument name="state" type="string" required="true">
<cfargument name="keywords" type="string" required="false">
<cfquery name="ServiceResults" datasource="#Request.MainDSN#">
Select company, name, phone, service, city, state, username FROM members
WHERE service = #arguments.service# AND state = '#arguments.state#'
<cfif len(trim(form.keywords)) GT 0>
AND keywords LIKE '#arguments.keywords#'
</cfif>
ORDER BY company ASC
</cfquery>
<cfreturn ServiceResults>
</cffunction>
</cfcomponent>

Can anyone tell me why? I have tried keywords LIKE #argument.keywords# and
keywords = #argument.keywords#

Thanks

--
Wally Kolcz
Developer / Support

[Dan_Bracuk]

Your problem is here:
AND keywords LIKE '#arguments.keywords#'

First, you are using the like keyword without wildcards. Second, if your users can submit more than one keyword in a textbox, your still have a logic problem.

Turn on debugging and submit any two keywords. Then look at the sql that gets generated. It probably doesn't resemble what you had hoped for.

[Newsgroup_User]

Thanks for the update.

Is there anyplace to find good information on how to create a working search
engine like that I have where a user can enter one or more keywords?

[Dan_Bracuk]

I have heard good things about the book Teach Yourself SQL in 10 Minutes by Ben Forta.

[Newsgroup_User]

Anything more specific such as a online tutorial or what to look for in
another SQL book?