SQL similar issue

[williamZanelli]

Hi guys,

 

I needed some help,

 

I want to query my db, but I want to search for something similar to a particular phrase,

What I want query is

 

"if a a record with the "username" field, similar to this field exists (say 80% similar) in my DB, bring me that record"

 

I'm not sure hot to phrase this in SQL, I'm using MySQL iif that is of help. Is this even possible?

 

If I cant do this in SQL is there another, efficient way of searching a database for this?

 

Thanks for your thoughts in advance

 

Will

 

 

 

 

[mike12255]

Im not promising anything here and idk if you can use a "wildcard" in a query but i'd try something like this:

 

$percent = celi((strlength*80)/100) = 80 percent of string

 

then substr to $percent and add a * at the end of the string and run that through the query? just an idea if im not to clear tell me and ill explain better

[Maq]

You can use something like LIKE or REGEXP.

 

I would also recommend reading this tutorial: http://www.phpfreaks.com/tutorial/simple-sql-search

[williamZanelli]

Hi guys

 

Thanks for your suggestions.

 

What I want to achieve is, someone searches some terms on my website, I return a result with percentage simialrity/relevance to search terms - I've seen this feature on a lot of websites - how is it implemented?

 

I could use %LIKE% but I'm concerned about the accuracy, for example - I have the string

 

"France's news", in my DB I have something like "Frances news" - %Like% would not pick this up??

 

Thanks for your thoughts on this

 

Will

[kickstart]

Hi

 

What you appear to want is a non name version of Soundex.

 

http://en.wikipedia.org/wiki/Soundex

 

The non name version is Metaphone and php supports it:-

 

http://uk.php.net/manual/en/function.metaphone.php

 

All the best

 

Keith

[o3d]

Hi guys

 

Thanks for your suggestions.

 

What I want to achieve is, someone searches some terms on my website, I return a result with percentage simialrity/relevance to search terms - I've seen this feature on a lot of websites - how is it implemented?

 

I could use %LIKE% but I'm concerned about the accuracy, for example - I have the string

 

"France's news", in my DB I have something like "Frances news" - %Like% would not pick this up??

 

Thanks for your thoughts on this

 

Will

 

You are looking for search engine software. There are companies that specialise on this topic and making millions.

 

There are some decent algorithms on the web and I would recommend you reading this - http://en.wikipedia.org/wiki/String_searching_algorithm.

 

If you want to implement a primitive search engine (in a query) try to remove special characters (',:#@!$%^&*, etc) and spaces, convert to lower case and possibly remove vowels and see what you get.

 

e.g.

before simple algorithm

$str_input = 'seerch texxt';

$str_reference = 'Some Searching Text';

 

after simple alogrithm

$str_input[0] = 'srch';

$str_input[1] = 'txxt';

$str_reference = 'smsrchngtxt';

 

That should return a 50% match as "srch" was found in the reference string.