Getting 'like' behavior in Select Match/Against

[snoopy6129]

I have built a product search feature for a website I am working on. The search feature works. But I would like to enhance it. Right now, for example, if someone where to enter the keyword duck in my 'product search engine', only records that has the exact spelling of duck are displayed. What I would like to do to enhance the search engine is to have it display records that also contains the word ducks (plural) or murduck, etc.

The SQL statement I've come up with (below) does not work as I would expect it. I've used both the % and the * as the wildcard operator. Still, only records with the exact spelling of duck are returned.

SELECT * 
FROM Products 
WHERE MATCH (ProductItemNumber, ProductName, ProductDescription) 
AGAINST ('%duck%')

Essentially, I'm trying to accomplish this:

   SELECT * FROM Products WHERE ProductName LIKE '%duck%'

but across multiple fields.

I am running MySQL 3.23.58 and from the various sites I've visited, this may not be possible. Please advise, suggestions, or if this is possible on MySQL 3.23.58, or if my syntax is wrong, etc.

Thank you all in advance.

 

[vacunita]

How about:

 SELECT * FROM Products WHERE ProductName LIKE '%duck%' OR ProductItemNumber='duck' OR ProductDescription LIKE '%duck%'

The OR operator is very useful.

I used an equal for ProductItemNumber since I assume its an integer, and LIKE only works for strings.



----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[snoopy6129]

Thank vacunita for the helpful tip. Your 'OR' idea works. Much appreciated it, thanks.

 

[Kozusnik]

Try this...

SELECT *
FROM Products
WHERE MATCH (ProductItemNumber, ProductName, ProductDescription)
AGAINST ('duck' IN BOOLEAN MODE)

Make sure fulltext index is set on those fields and you have your min word length at 4 or less.

Mark

 

[guelphdad]

your version of mysql is absolutely ancient, are you running windows 95 too?

boolean mode for full text searches is not supported in mysql 3

 

[ynott]

string value in Select statement?

DIM EntryNo is set
EntryNo = ""

This first select statement against a SQL DB works:

SQL6 = "SELECT NO, LNAME, FNAME, MNAME, DOB"
SQL6 = SQL6 & " from COMP9.DBO.proc"
SQL6 = SQL6 & " Where NO like '00010044%'"
set rs6 = my_conn.Execute(SQL6)

However, this is what I'm trying to get to work when "EntryNo" is set at 00010044 without luck:

SQL6 = "SELECT NO, LNAME, FNAME, MNAME, DOB"
SQL6 = SQL6 & " from COMP9.DBO.proc"
SQL6 = SQL6 & " Where NO like ' & EntryNo & %'"
set rs6 = my_conn.Execute(SQL6)

Maybe the "NO" is not a string, but another type of field. Any help?

TIA

 

[HarleyQuinn]

You're missing a "

SQL6 = SQL6 & " Where NO like '[red]"[/red] & EntryNo & %'"

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

http://lessthandot.com

 

[HarleyQuinn]

Sorry, I see you need another, you were originally passing in the value & EntryNo & rather than the value of EntryNo.

SQL6 = SQL6 & " Where NO like '[red]"[/red] & EntryNo & [red]"[/red]%'"

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

http://lessthandot.com