SitePoint Sponsor

User Tag List

Results 1 to 16 of 16
  1. #1
    SitePoint Member
    Join Date
    Jun 2010
    Posts
    10
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Problem with search

    I have a phonebook program which essentially takes a search and goes through a data base to find matches and displays the results. I am using full text searching but my problem is that if I search for an exact name, The program will display everything that is close to the search BUT the exact name.

    eg: If i search "bob smith" The results will display everything close to bob smith... such as "bob smithe, rob smith, etc..." but will not display "bob smith"

    If i search "rob smith", bob smith will be displayed in the result but not "rob smith".

    Thanks for the help!

  2. #2
    SitePoint Zealot
    Join Date
    Jan 2006
    Location
    Gold Coast, Australia
    Posts
    123
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Post your code so far. In particular your SQL query.

  3. #3
    SitePoint Member
    Join Date
    Jun 2010
    Posts
    10
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    $result = MySQL_query("SELECT * FROM $table WHERE MATCH (Class,Name)AGAINST ('$name')");

    Name is... name and class holds "tags" such as "construction, art store etc.."

  4. #4
    SitePoint Zealot
    Join Date
    Jan 2006
    Location
    Gold Coast, Australia
    Posts
    123
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Try and add this:

    AGAINST ('$name*')");

    Notice the *


    Although I would advise you to approach this using the Stemming process. I believe there is a mysql plugin that implements it.

  5. #5
    SitePoint Member
    Join Date
    Jun 2010
    Posts
    10
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    the * did not work but i will look up the stemming process thanks!

  6. #6
    SitePoint Zealot
    Join Date
    Jan 2006
    Location
    Gold Coast, Australia
    Posts
    123
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    After some digging around I found IN BOOLEAN MODE

    Sooo try:

    AGAINST ('$name*' IN BOOLEAN MODE)"

  7. #7
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,032
    Mentioned
    152 Post(s)
    Tagged
    2 Thread(s)
    The wildcard to use with AGAINST is %, not *

    Rémon - Hosting Advisor

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  8. #8
    SitePoint Member
    Join Date
    Jun 2010
    Posts
    10
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks! I used % and boolean mode and it seems work in finding the proper match words. Though now I have the problem that it is displaying alphabetically and not by relevance. Is there a way to ORDER BY relevance?

    I tried something like:

    $result = MySQL_query("SELECT *,MATCH (Class,Name)AGAINST ('$name%') as Relevance FROM $table WHERE MATCH (Class,Name)AGAINST ('$name%'IN BOOLEAN MODE) HAVING Relevance > 0.2 Order by Relevance Desc");

    But it does not work.

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by ScallioXTX View Post
    The wildcard to use with AGAINST is %, not *
    you sure? got a reference to the manual that shows this?

    i think it's the other way round
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Zealot
    Join Date
    Jan 2006
    Location
    Gold Coast, Australia
    Posts
    123
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The % is a LIKE wildcard and has nothing to do with the MATCH() ... AGAINST().

  11. #11
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,032
    Mentioned
    152 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by wackyjoe View Post
    The % is a LIKE wildcard and has nothing to do with the MATCH() ... AGAINST().
    You're right, I confused the two.
    Thanks for clearing it (and my head) up
    Rémon - Hosting Advisor

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  12. #12
    SitePoint Member
    Join Date
    Jun 2010
    Posts
    10
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Im sorry for reviving the thread but I am still stuck. If i search "IN BOOLEAN MODE" I does find an exact match (Plus others) but does not sort by relevance... But if I search without boolean mode, It doesn't find an exact match BUT it does sort by relevance desc.

    Anyone know what to do? thanks

  13. #13
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    you could try not using fulltext searching, and use LIKE instead

    i would not expect a "phonebook program" to have large text columns that are amenable to fulltext searching
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  14. #14
    SitePoint Member
    Join Date
    Jun 2010
    Posts
    10
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The only reason I used the full text search was because not only do i need to search through the names, but also certain "tags" that are associated with each name... such as "painting, construction etc" this is all part of the yellow pages section.

  15. #15
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    you would still be able to use LIKE on your tags (which might actually be better normalized, rather than being stored in a comma-delimited list)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  16. #16
    SitePoint Member
    Join Date
    Jun 2010
    Posts
    10
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I finally solved the problem bluntly by using LIKE and also searching by FULL TEXT... apostrophes gave me some trouble but i solved it using an str_replace fragment... Thank you everyone for the help!


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •