fuzzy search

Hi everyone,

Can anyone give pointers on doing fuzzy searches in mysql.

Currently when people are entering an address, it will check the town
and county to make sure they match. However if they dont, then i want
it to come back with a list of "possibilities"

I have tried the MATCH / AGAINST and this sort of works, but will miss
out some really good possibilites like this:

There is a town called Brierley and a town called Briery
There is a town called Brierley Hill and a town called Briery Hill

If someone searched for Breirly then none of the above would come back

Am i missing something obvious?

Thanks in advance
Dave

On 8 Aug, 10:36, Dave <david.greenh...@praybourne.co.uk> wrote:
> Hi everyone,
>
> Can anyone give pointers on doing fuzzy searches in mysql.
>
> Currently when people are entering an address, it will check the town
> and county to make sure they match. However if they dont, then i want
> it to come back with a list of "possibilities"
>
> I have tried the MATCH / AGAINST and this sort of works, but will miss
> out some really good possibilites like this:
>
> There is a town called Brierley and a town called Briery
> There is a town called Brierley Hill and a town called Briery Hill
>
> If someone searched for Breirly then none of the above would come back
>
> Am i missing something obvious?
>
> Thanks in advance
> Dave



Me again, I managed to get soundex working (though the information on
MySQL website is not really helpful) This is bringing back a lot more
than anything else i tried, but now its too many and some which
shouldnt really be there.

Is there a way i can pipe the results through something else to narrow
it down?

Thanks
Dave.

Did you try "FULL TEXT SEARCH"? It comes back with the probability of
the match. Quite strong in finding matches.
Note that it is only available with myISAM type tables.

On 8 Aug, 13:23, Arakrys <Arak...@gmail.com> wrote:
> Did you try "FULL TEXT SEARCH"? It comes back with the probability of
> the match. Quite strong in finding matches.
> Note that it is only available with myISAM type tables.

Hi Arakrys,

Well I am not sure (I am more of a beginner with SQL) I have searched
google, and the MySQL website, and according to what i find, I believe
they are suggesting to use MATCH / AGAINST. If this is the case, then
i already tried this:

Below is what i tried and the results:
Searched for 'town' Brierley

$compare = mysql_query("SELECT * FROM towns_tbl
JOIN county_tbl ON towns_tbl.county_id = county_tbl.county_id
WHERE (`town`) RLIKE('$town') ORDER BY town")

This brought back 3 towns:
Brierley - Gloucestershire
Brierley - Herefordshire
Brierley Hill - West Midlands

$compare = mysql_query("SELECT * FROM towns_tbl
JOIN county_tbl ON towns_tbl.county_id = county_tbl.county_id
WHERE MATCH(`town`) AGAINST('$town') ORDER BY town")

Again this brought back 3 towns:
Brierley - Gloucestershire
Brierley - Herefordshire
Brierley Hill - West Midlands

$compare = mysql_query("SELECT * FROM towns_tbl
JOIN county_tbl ON towns_tbl.county_id = county_tbl.county_id
WHERE SOUNDEX(`town`) = SOUNDEX('$town') ORDER BY town")

This brought back 47 towns (which i wont list for wasting space)
However, two of the towns which people would associate as being
closest would be:

Briery Hill - Blaenau Gwent/Blaenau Gwent in Wales/Cymru
Bierley - Isle of Wight

I am hoping someone could make a suggestion. I have read that
something in PHP called levenshtein could possibly work, but reading
the comments, its not ideal as it only brings back numbers (maybe this
is wrong)

Dave.