Problem with search

Databases
1

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

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

3

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)

4

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!

5

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.

6

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

7

the * did not work but i will look up the stemming process thanks! :slight_smile:

8

The wildcard to use with AGAINST is %, not *

:slight_smile:

9

You’re right, I confused the two.
Thanks for clearing it (and my head) up :slight_smile:

10

After some digging around I found IN BOOLEAN MODE

Sooo try:

AGAINST (‘$name*’ IN BOOLEAN MODE)"

11

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.

12

$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…”

13

Post your code so far. In particular your SQL query.

14

The % is a LIKE wildcard and has nothing to do with the MATCH() … AGAINST().

15

you sure? got a reference to the manual that shows this?

i think it’s the other way round :slight_smile:

16

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.