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!
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
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)
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!
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.
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
the * did not work but i will look up the stemming process thanks!
The wildcard to use with AGAINST is %, not *
You're right, I confused the two.
Thanks for clearing it (and my head) up
After some digging around I found IN BOOLEAN MODE
AGAINST ('$name*' IN BOOLEAN MODE)"
Try and add this:
Notice the *
Although I would advise you to approach this using the Stemming process. I believe there is a mysql plugin that implements it.
$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.."
Post your code so far. In particular your SQL query.
The % is a LIKE wildcard and has nothing to do with the MATCH() ... AGAINST().
you sure? got a reference to the manual that shows this?
i think it's the other way round
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.