[SOLVED] Clever search

[jaymc]

Whats the best way to accurately yet loosely search in mysql, without falling over like below example

 

# actual field data = I havnt got any without the apostrophy

$string = "I havn't got any";

 

$q = "SELECT * FROM tablename WHERE fieldname LIKE '%$string%'"

 

 

I would really like it to pick up anything thats close to that, may return rows where the field is any of these

 

* I havnt got

* havnt got any

 

And also return the data sorted by closest match

 

Hope someone can help

[dptr1988]

You could try replacing the apostrophe ( and any other char that you want to disregard in the search) with a '%' symbol.

 

I don't think that there are any 'looser' search methods that MySQL can do for you.

 

It sounds like you will need to select all of the rows and do the search your self.

[fenway]

Or you can simply replace all the apostrophes with blanks on either end.

[activeserver]

does this give some clues:

1. http://php.net/soundex          and      http://www.w3schools.com/php/func_string_soundex.asp

and

2. http://php.net/levenshtein      and    http://www.w3schools.com/php/func_string_soundex.asp

 

If things work well for you, please be kind to post the results. thanks

 

[jaymc]

You would have to pull out the result from the database first though to do the php check

 

Not very feasable unless you could use that function within mysql..?

[fenway]

What's wrong with:

 

SELECT * FROM tablename WHERE REPLACE( fieldname, "'", "") LIKE '%$string%

[jaymc]

Because the apostrophy was just an example

 

A comma could be another, or a fullstop etc

 

Anything that a user would not be fussy about but mysql would

 

I need users to be able to talk to my database humanly, not logically

[fenway]

Then you'll have to replace all of the non-text characters with multiple replaces (or an SP).

[jaymc]

Ok, so I guess that kind of takes care of those type of chars

 

But what if there is a data in the DB for example "FOOTBALL" and they search for "FOOTBALLS"

 

Return is none even though to us they are practically the same

 

I need to loosen it up

[fenway]

Then you either need to use Lucene (a 3rd-party solution) or SOUNDEX... but the latter is very tricky depending on where the typo/extra characters are.

[jaymc]

Thanks