sql search problem

[grabit]

Hi Folks
I have the following code in my search results page, however it isreturning results of partial words ie search for "hock" and it returns "hocking", search for "test" and it returns "attested" can someone pls help with how i can overcome this please.
It also appears that it is not finding a result if the search criteria is either the first or last word in a sentance.

code is:

SELECT ID, category, bustype, busname, buscontact, busphone, busaddress1, spdesc, sptext, listtext
FROM listings
WHERE category LIKE '% #form.subject# %' OR category LIKE '% #form.subject#_' OR category LIKE '_#form.subject# %' OR category LIKE '% _#form.subject#_ %'
    OR bustype LIKE '% #form.subject# %' OR bustype LIKE '% #form.subject#_' OR bustype LIKE '_#form.subject# %' OR bustype LIKE '% _#form.subject#_ %'
    OR busname LIKE '% #form.subject# %' OR busname LIKE '% #form.subject#_' OR busname LIKE '_#form.subject# %' OR busname LIKE '% _#form.subject#_ %'
    OR busaddress1 LIKE '% #form.subject# %' OR busaddress1 LIKE '% #form.subject#_' OR busaddress1 LIKE '_#form.subject# %' OR busaddress1 LIKE '% _#form.subject#_ %'
    OR buscontact LIKE '% #form.subject# %' OR buscontact LIKE '% #form.subject#_' OR buscontact LIKE '_#form.subject# %' OR buscontact LIKE '% _#form.subject#_ %'
	OR spdesc LIKE '% #form.subject# %' OR spdesc LIKE '% #form.subject#_' OR spdesc LIKE '_#form.subject# %' OR spdesc LIKE '% _#form.subject#_ %'
    OR sptext LIKE '% #form.subject# %' OR sptext LIKE '% #form.subject#_' OR sptext LIKE '_#form.subject# %' OR sptext LIKE '% _#form.subject#_ %'
    OR listtext LIKE '% #form.subject# %' OR listtext LIKE '% #form.subject#_' OR listtext LIKE '_#form.subject# %' OR listtext LIKE '% _#form.subject#_ %'
ORDER BY busname ASC

Any help would be greatly appreciated
Thanks in advance
Grabit

[MMcCarthy]

Hey grabit,

You need to interject a space before and after the search words.

Mary

P.S. You posted this in the articles sections so I've moved it to the forum.

[acoder]

How about something like:
[CODE=sql]category LIKE '% #form.subject# %' OR category LIKE '#form.subject# %' OR category LIKE '% #form.subject#'[/CODE]
This should match the word in the middle of a sentence, at the beginning and at the end.

As a side note, you should be careful when using form variables in queries. You don't want to be the victim of an SQL injection or cross-site scripting attack, so you should validate and encode all user input.