sql search problem

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • grabit
    New Member
    • Mar 2007
    • 22

    sql search problem

    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:
    Code:
    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
    Last edited by NeoPa; Oct 4 '07, 07:48 PM. Reason: Please use [CODE] tags
  • MMcCarthy
    Recognized Expert MVP
    • Aug 2006
    • 14387

    #2
    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.

    Comment

    • acoder
      Recognized Expert MVP
      • Nov 2006
      • 16032

      #3
      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.

      Comment

      Working...