How to do an almost matching search

MichaelHe

How do I search a MySQL database for almost matching hits.
Ex. finding an artist in the database called Bon Jovi by searching for Bon Jovy

I know about the similar text function and it actually work very well. Here I can set a % on how good a match I want.
But if I use similar text it has to run all entries from the database and with a rather large database this would not be a good solution.

So can the similar text function be used in a query line like Select * from dbname where similar_text($artistsearch,$artist,$percent) ..... or something like that...

Or is there another way to do an almost search so that people can find the artist even with spelling errors ?

Hope someone can help

Michael

developerNC

Have you tried a drop down menu with the $artistsearch, $artist, $percent and a textfield for the searchString%. This would narrow it down quite a bit.

MichaelHe

I'm not quite sure I understand :o)
$artistsearch is the searchstring ex. Bon Jovy
$artist is the artist name in the database ex. Bon Jovi
$percent would be the percent of how correct it should be, which work very well with the similar texr function.

But I don't quite know what you mean by textfield for the searchString%

developerNC

I'm sorry I misunderstood your question. Here is a link to MySQL search patterns if you are using MySQL. This might give you an idea of what you need to do. Hope this helps.

http://dev.mysql.com/doc/refman/5.0/en/pattern-matching.html

MichaelHe

Like, Regexp and Rlike are all great fuctions, but they only find correctly spelt words.
Non of them will find Bon Jovi search for Jon Bovy.
If a similar_text function is set to ex. 80% it would make Bon Jovy a match.
But to use this function I have to run the entire database and then match the search to the artist name in the database, and then echo the name on the screen if the similar_text function find them to match.. like this

$artist=mysql_query("SELECT * FROM db ORDER BY artistname");
while ($row = mysql_fetch_array($artist)){$artistname=$row[artistname];
similar_text($artistsearch,$artistname,$percent);
IF ($percent > 80) {echo $artistname;}
}

This will only echo the artistnames more than 80% correct compared to the search and it would print Bon Jovi when searching for Bon Jovy.
BUT.... I have to run the entire database.

So isn't it possible in any way to put the similar_text functino in the query line or use some other function in the query line that matches like similar_text and not like regexp and like... which both matches only 100% correct spelling.