CODEWITHSUNDEEP
sqlmatchsql-like
Paul Bedford
Paul Bedford

Reputation: 71

sql search function issue, LIKE and MATCH

I had to fix a problem in someone else's code where a search function was returning too many results, I noticed it was using MATCH< where LIKE would be more appropriate.

I am not too good on SQL and I have changed all the MATCH's to LIKE's..

This has obviously brought on an error, I assume its to do with the AGAINST which im not sure but could be only be working with MATCH..

Here is the statement in the asp code.

    strSQL = ""
        strSQL  = "SELECT 'case-studies' AS pagetype, cs.title AS entrytitle, cs.casestudyid AS id, csi.casestudyimageid AS imageid, csi.imagetitle,  LIKE (cs.title, cs.content) AGAINST ('" & searchfor & "') AS relevance, LIKE (csi.imagetitle) AGAINST ('" & searchfor & "') AS imagerelevance "
        strSQL = strSQL & "FROM casestudies AS cs, casestudiesimages AS csi "
        strSQL = strSQL & "WHERE cs.casestudyid = csi.casestudyid AND cs.modestatus = '1' AND ((LIKE(cs.title, cs.content) AGAINST ('" & searchfor & "' IN BOOLEAN MODE)) OR (LIKE(csi.imagetitle) AGAINST ('" & searchfor & "' IN BOOLEAN MODE)) OR (cs.title like '%" & searchfor & "%') OR (cs.content like '%" & searchfor & "%') OR (csi.imagetitle like '%" & searchfor & "%')) "
        strSQL = strSQL & "UNION ALL "
        strSQL = strSQL & "SELECT 'news' AS pagetype, news.title AS entrytitle, news.newsid AS id, newsimages.newsimageid AS imageid, newsimages.imagetitle, LIKE (news.title, news.content) AGAINST ('" & searchfor & "') AS relevance, LIKE (newsimages.imagetitle) AGAINST ('" & searchfor & "') AS imagerelevance "
        strSQL = strSQL & "FROM news, newsimages "
        strSQL = strSQL & "WHERE news.newsid = newsimages.newsid AND news.modestatus = '1' AND ((LIKE(news.title, news.content) AGAINST ('" & searchfor & "' IN BOOLEAN MODE)) OR (LIKE(newsimages.imagetitle) AGAINST ('" & searchfor & "' IN BOOLEAN MODE)) OR (news.title like '%" & searchfor & "%') OR (news.content like '%" & searchfor & "%') OR (newsimages.imagetitle like '%" & searchfor & "%')) "          
        strSQL = strSQL & "GROUP BY id "
        strSQL = strSQL & "ORDER BY relevance DESC, imagerelevance DESC "

Can anyone help decipher this code.. It needs to search on like for like, as opposed searching "he" and getting "help" etc

Thanks

Upvotes: 0

Views: 104

Answers (2)

ron tornambe
ron tornambe

Reputation: 10780

If you are using MyISAM and there are full-text indexes on the the columns you wish to search, you can avoid full-table scans by using "MATCH....AGAINST" rather than LIKE, which is much more efficient. I have replaced the "LIKEs" in those places where MATCH is appropriate - followed by AGAINST in your code. The general rule with your SQL is, if the expression to test does not have wildcard characters (ONLY % in this case), use MATCH. 

strSQL  = "SELECT 'case-studies' AS pagetype, cs.title AS entrytitle, 
          cs.casestudyid AS id, csi.casestudyimageid AS imageid, csi.imagetitle,
          MATCH (cs.title, cs.content) AGAINST ('" & searchfor & "') AS relevance, 
          MATCH (csi.imagetitle) AGAINST ('" & searchfor & "') AS imagerelevance "
strSQL = strSQL & "FROM casestudies AS cs, casestudiesimages AS csi "
strSQL = strSQL & "WHERE cs.casestudyid = csi.casestudyid AND cs.modestatus = '1' AND
    ((MATCH (cs.title, cs.content) AGAINST ('" & searchfor & "' IN BOOLEAN MODE)) OR
    (MATCH (csi.imagetitle) AGAINST ('" & searchfor & "' IN BOOLEAN MODE)) OR 
    (cs.title LIKE '%" & searchfor & "%') OR (cs.content LIKE '%" & 
     searchfor & "%') OR (csi.imagetitle LIKE '%" & searchfor & "%')) "
strSQL = strSQL & "UNION ALL "
    strSQL = strSQL & "SELECT 'news' AS pagetype, news.title AS entrytitle, 
        news.newsid AS id, newsimages.newsimageid AS imageid, newsimages.imagetitle, 
        MATCH (news.title, news.content) 
        AGINST ('" & searchfor & "') AS relevance, 
        MATCH (newsimages.imagetitle) 
        AGAINST ('" & searchfor & "') AS imagerelevance "
    strSQL = strSQL & "FROM news, newsimages "
    strSQL = strSQL & "WHERE news.newsid = newsimages.newsid AND news.modestatus = '1'
         AND ((MATCH (news.title, news.content) 
                 AGAINST ('" & searchfor & "' IN BOOLEAN MODE)) OR
        (MATCH (newsimages.imagetitle) AGAINST ('" & searchfor & "' 
        IN BOOLEAN MODE)) OR (news.title LIKE '%" & searchfor & "%') 
        OR (news.content LIKE '%" & searchfor & "%') 
        OR (newsimages.imagetitle LIKE '%" & searchfor & "%')) "          
    strSQL = strSQL & "GROUP BY id "
    strSQL = strSQL & "ORDER BY relevance DESC, imagerelevance DESC "

Upvotes: 0

Stijn Wynants
Stijn Wynants

Reputation: 107

You are saying LIKE but you are not comparing your LIKE to anything, a valid LIKE statement is

SELECT *
FROM CUSTOMERS
WHERE CUSTOMERNAME LIKE '%' + @Param+ '%'

Upvotes: 1

Related Questions