search with more keyword

[maximos]

Hi all,
i have a search page (results are coming from ms database) and a result page,
lets say i want to search for the word "computer" if i enter any computer, or any letter less, it works fine, but if i enter computerS , doesn't return with any result, meaing that if i enter any character(s) extra doesnt' work , even if enter a space after the keyword,

i guess most user would enter 2 or more keyword, is there anyways i could make the query (or any IF statment) will look any words, not all ??

Thanks in advance,

here is my query statmement,

<!--- pulling my data --->
<cfquery name=&quot;qItems&quot; dataSource=&quot;directory&quot;>
SELECT * FROM tblCategories, tblItems
WHERE tblCategories.CategoryID = tblItems.CategoryIDFK


<cfif BusinessDescription NEQ &quot;&quot;>
and tblItems.BusinessDescription Like'%#FORM.BusinessDescription#%'



max

 

[ecobb]

You may want to check into running a verity search instead of just running a query with &quot;LIKE&quot;.

 

[maximos]

Can you point me to some resources web site to get more info on that , it would be great

max

 

[ecobb]

You can get a pretty good bit of info on verity in the ColdFusion help files that come with CF Studio or Dreamweaver MX. There's also a pretty good FAQ here on Tek-Tips: faq232-214

Hope this Helps!

 

[ETN]

verity is hard and complex.. you can just set all your string to lower or higher...

i.e
and LOWER(tblItems.BusinessDescription) Like'%#Lcase(FORM.BusinessDescription)#%'

 

[TruthInSatire]

your problem is pretty simple to solve, and easy to understand.

first the problem.

word in db >>> Computer
word searched for >>> Computers
your query looks for anything that matches the entered string and may or may not have extra text before or after it. Since Computers does not match Computer you're never going to match it.

The solution:
option 1) if it's a small db simply add a new column called something like "extraSearch" and enter plurl words or an abv of the words if they have one.

ex
Entry in DB >>> "This computer has a great 19 inch monitor and Compact Disk re-Writer Drive."
Entry in "extraSearch" >>> computers in. cdr cd-r

change your query to

and tblItems.BusinessDescription Like'%#FORM.BusinessDescription#%'
or extraSearch Like'%#FORM.BusinessDescription#%'

Option 2)
if its a large db, check the word being searched and see if the last leter is an "S". Assume that eny word that ends in "S" is plural and remove it.

<cfif right(form.businessDescription, 1) eq "s">
<cfset form.businessDescription = left(form.businessDescription, len(form.businessDescription)-1>

and Keep your query as it is.
The advange is it's easy and less code than option one. however option 1 can be more customized for your search.

Beware of programmers who carry screwdrivers.

 

[imstillatwork]

Using a keywords field in your db is a great idea. I do this too. It allows you to write your titles and descriptions of items in plain english, and have the ability to accuratly search for it.

I run a search query that treats the search string as a space delimited list, so you can loop through it to create your WHERE clause on each individual term. That also allows you to add a search all words or search on any word feature. All you have to do is change your WHERE...AND...AND to WHERE..OR..OR..

Works great for small dbs. I use it on shopping sites with less than 1000 items or so.